Один з моїх колег запитав мене, чи можна зробити декілька виділень у розкривному списку в Excel.
Коли ви створюєте випадаючий список, ви можете зробити лише один вибір. Якщо вибрати інший елемент, перший замінюється новим виділенням.
Він хотів зробити декілька виділень з того самого спадного меню таким чином, щоб виділення були додані до вже наявного значення в комірці.
Щось, як показано нижче на фото:
Це неможливо зробити за допомогою вбудованих функцій Excel.
Єдиний спосіб - використовувати код VBA, який запускається щоразу, коли ви робите вибір, і додає вибране значення до існуючого значення.
Перегляд відео - Як вибрати декілька елементів зі спадного списку Excel
Як зробити кілька виділень у випадаючому списку
У цьому підручнику я покажу вам, як зробити декілька виділень у розкривному списку Excel (з повторенням і без повторення).
Це був один з найпопулярніших підручників Excel на цьому сайті. Оскільки я отримую багато подібних питань, я вирішив створити розділ поширених запитань в кінці цього підручника. Тож якщо після прочитання цього питання у вас виникнуть запитання, спочатку ознайомтесь із розділом поширених запитань.Створення розкривного списку, який дозволяє кілька варіантів, складається з двох частин:
- Створення розкривного списку.
- Додавання коду VBA до внутрішнього сервера.
Створення розкривного списку в Excel
Нижче наведено кроки для створення розкривного списку в Excel:
- Виберіть клітинку або діапазон клітинок, де ви хочете, щоб з'явився випадаючий список (C2 у цьому прикладі).
- Перейдіть до Дані -> Інструменти даних -> Перевірка даних.
- У діалоговому вікні «Перевірка даних» на вкладці налаштувань виберіть «Список» як критерії перевірки.
- У полі Джерело у спадному меню виберіть клітинки, у яких є потрібні елементи.
- Натисніть OK.
Тепер у клітинці С2 є випадаючий список, у якому відображаються назви елементів у форматі А2: А6.
Наразі у нас є випадаючий список, де ви можете вибирати один елемент за раз (як показано нижче).
Щоб увімкнути цей випадаючий список, щоб ми могли зробити кілька варіантів вибору, нам потрібно додати код VBA у зворотному кінці.
Наступні два розділи цього підручника дадуть вам код VBA, щоб дозволити декілька виділень у випадаючому списку (з повторенням і без).
Код VBA, що дозволяє декілька виділень у випадаючому списку (з повторенням)
Нижче наведено код Excel VBA, який дозволить нам вибрати декілька елементів зі спадного списку (що дозволяє повторювати вибір):
Private Sub Worksheet_Change (ByVal Target As Range) 'Код від Sumit Bansal з https://trumpexcel.com' Щоб зробити багаторазові виділення у випадаючому списку в Excel Затемнити старе значення як рядок Змінити нове значення як рядок при помилці Перейти до Exitsub Якщо Target.Address = "$ C $ 2" Тоді, якщо Target.SpecialCells (xlCellTypeAllValidation) нічого, то GoTo Exitsub Інше: Якщо Target.Value = "" Тоді GoTo Exitsub Else Application.EnableEvents = False Newvalue = Target.Value Application.Undo Oldvalue = Target.Value. If Oldvalue = "" Тоді Target.Value = Newvalue Else Target.Value = Oldvalue & "," & Newvalue Кінець Якщо закінчиться Якщо закінчиться Якщо Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub
Тепер вам потрібно розмістити цей код у модулі у редакторі VB (як показано нижче в розділі «Де розмістити код VBA»).
Після того, як ви розмістили цей код у бекенді (розглянутому далі в цьому підручнику), він дозволить вам зробити декілька варіантів у спадному меню (як показано нижче).
Зауважте, що якщо ви обираєте елемент більше одного разу, він буде введений знову (допускається повторення).
Спробуйте самі… Завантажте приклад файлу
Код VBA, що дозволяє декілька виділень у випадаючому списку (без повторення)
Багато людей запитують про код для вибору кількох елементів зі спадного списку без повторення.
Ось код, який переконається, що елемент можна вибрати лише один раз, щоб не повторювалося:
Private Sub Worksheet_Change (ByVal Target As Range) 'Code by Sumit Bansal from https://trumpexcel.com' Щоб дозволити декілька виділень у випадаючому списку в Excel (без повторення) Dim Oldvalue As String Dim Newvalue As String Application.EnableEvents = Помилка True On Помилка GoTo Exitsub If Target.Address = "$ C $ 2" Тоді, якщо Target.SpecialCells (xlCellTypeAllValidation) нічого, то GoTo Exitsub Else: If Target.Value = "" Тоді GoTo Exitsub Else Application.EnableEvents = False Newvalue = Target. Значення Application.Undo Oldvalue = Target.Value If Oldvalue = "" Тоді Target.Value = Newvalue Else Якщо InStr (1, Oldvalue, Newvalue) = 0 Тоді Target.Value = Oldvalue & "," & Newvalue Else: Target.Value = Oldvalue Кінець Якщо Кінець Якщо Кінець Якщо Закінчення Якщо Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub
Тепер вам потрібно розмістити цей код у модулі у редакторі VB (як показано в наступному розділі цього підручника).
Цей код дозволить вам вибрати кілька елементів зі спадного списку. Однак ви зможете вибрати елемент лише один раз. Якщо ви спробуєте вибрати його знову, нічого не станеться (як показано нижче).
Спробуйте самі… Завантажте приклад файлу
Де розмістити код VBA
Перш ніж почати використовувати цей код у excel, вам потрібно розмістити його в бекенді, щоб він запускався, коли відбуваються будь-які зміни у випадаючому списку.
Виконайте наведені нижче кроки, щоб помістити код VBA у бекенд Excel:
- Перейдіть на вкладку «Розробник» і натисніть на Visual Basic (також можна скористатися комбінацією клавіш - Alt + F11). Відкриється редактор Visual Basic.
- Ліворуч повинна бути панель Провідника проектів (якщо її немає, використовуйте Control + R, щоб зробити її видимою).
- Двічі клацніть на імені робочого аркуша (на лівій панелі), де знаходиться розкривний список. Відкриється вікно коду для цього аркуша.
- У вікні коду скопіюйте та вставте наведений вище код.
- Закрийте редактор VB.
Тепер, коли ви повернетесь до випадаючого списку та зробите вибір, це дозволить вам зробити кілька варіантів (як показано нижче):
Спробуйте самі… Завантажте приклад файлу
Примітка: Оскільки для цього ми використовуємо код VBA, вам потрібно зберегти книгу з розширенням .xls або .xlsm.
Часті запитання (FAQ)
Я створив цей розділ, щоб відповісти на деякі з найпоширеніших питань щодо цього підручника та коду VBA. Якщо у вас є запитання, я прошу вас спочатку ознайомитися з цим списком запитів.
З: У коді VBA функціональність призначена лише для комірки С2. Як я можу отримати його для інших клітин? Відповідь: Щоб отримати цей розкривний список множинного вибору в інших клітинках, вам потрібно змінити код VBA у бекенді. Припустимо, ви хочете отримати це для C2, C3 та C4, вам потрібно замінити наступний рядок у коді: If Target.Address = "$ C $ 2" Тоді цим рядком: If Target.Address = "$ C $ 2" Або Target.Address = "$ C $ 3" Або Target.Address = "$ C $ 4" Тоді
З: Мені потрібно створити кілька випадаючих меню у всьому стовпці "С". Як я можу отримати це для всіх клітинок у стовпцях із функцією багаторазового вибору? Відповідь: Щоб увімкнути багаторазовий вибір у випадаючих списках у цілому стовпці, замініть наступний рядок у коді: If Target.Address = "$ C $ 2" Тоді цим рядком: If Target.Column = 3 Тоді у подібних рядках, якщо Ви хочете, щоб ця функція була у стовпцях C і D, використовуйте наступний рядок: Якщо Target.Column = 3 або Target.Column = 4 Тоді
З: Мені потрібно створити кілька випадаючих меню поспіль. Як я можу це зробити? Відповідь: Якщо вам потрібно створити випадаючі списки з кількома виділеннями поспіль (скажімо, другий рядок), вам потрібно замінити наступний рядок коду: Якщо Target.Address = "$ C $ 2" Тоді цим рядком: Якщо Target.Row = 2 Тоді так само, якщо ви хочете, щоб це працювало для кількох рядків (скажімо, другого та третього рядків), замість цього використовуйте наступний рядок коду: If Target.Row = 2 або Target.Row = 3 Тоді
З: Наразі декілька виділень розділені комою. Як я можу змінити це, щоб розділити їх пробілом (або будь -яким іншим роздільником). Відповідь: Щоб розділити їх роздільником, відмінним від коми, потрібно замінити наступний рядок коду VBA: Target.Value = Oldvalue & "," & Newvalue на цей рядок коду VBA: Target.Value = Oldvalue & "" & Newvalue Подібним чином, якщо ви хочете змінити кому іншим символом, наприклад |, ви можете використовувати такий рядок коду: Target.Value = Oldvalue & "|" & Newvalue
З: Чи можу я отримати кожне виділення в окремому рядку в тій самій комірці? Відповідь: Так, можна. Щоб отримати це, вам потрібно замінити наведений нижче рядок коду VBA: Target.Value = Oldvalue & "," & Newvalue на цей рядок коду: Target.Value = Oldvalue & vbNewLine & Newvalue vbNewLine вставляє новий рядок у ту ж клітинку . Тому щоразу, коли ви робите вибір зі спадного меню, він буде вставлений у новий рядок.
З: Чи можу я зробити так, щоб функція множинного вибору працювала на захищеному аркуші? Відповідь: Так, можна. Щоб це зробити, вам потрібно зробити дві речі: Додати наступний рядок у код (відразу після оператора DIM): Me.Protect UserInterfaceOnly: = Правда По -друге, вам потрібно переконатися, що клітинки, які мають спадне меню з функціями множинного вибору, не заблоковані, коли ви захищаєте весь аркуш. Ось підручник про те, як це зробити: Заблокувати клітинки в Excel
