Як створити випадаючий список із декількома виділеннями або значеннями в Excel?

За замовчуванням у розкривному списку перевірки даних у Excel можна вибрати лише один елемент. Як ви можете зробити кілька виборів у розкривному списку, як показано на малюнку нижче? Методи, наведені в цій статті, можуть допомогти вам вирішити проблему.

Створіть випадаючий список із декількома варіантами вибору за допомогою коду VBA

У цьому розділі наведено два коди VBA, які допоможуть вам зробити кілька варіантів у розкривних списках на аркуші. Два коди VBA можуть досягти:

Ви можете застосувати один із наведених нижче кодів VBA, щоб зробити кілька варіантів у розкривному списку на аркуші в Excel. Будь ласка, зробіть наступне.

1. Відкрийте робочий аркуш, що містить розкривні списки перевірки даних, з яких потрібно зробити кілька варіантів вибору. Клацніть правою кнопкою миші вкладку аркуша та виберіть Переглянути код з контекстного меню.

2 В Microsoft Visual Basic для додатків вікно, скопіюйте наведений нижче код VBA у вікно коду. Дивіться знімок екрана:

Код VBA 1: дозволити кілька варіантів вибору в розкривному списку без дублікатів

Private Sub Worksheet_Change(ByVal Target As Range)
    'Updated by Extendoffice 2019/11/13
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    If Target.Count > 1 Then Exit Sub
    On Error Resume Next
    Set xRng = Cells.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If Not Application.Intersect(Target, xRng) Is Nothing Then
        xValue2 = Target.Value
        Application.Undo
        xValue1 = Target.Value
        Target.Value = xValue2
        If xValue1 <> "" Then
            If xValue2 <> "" Then
                If xValue1 = xValue2 Or _
                   InStr(1, xValue1, ", " & xValue2) Or _
                   InStr(1, xValue1, xValue2 & ",") Then
                    Target.Value = xValue1
                Else
                    Target.Value = xValue1 & ", " & xValue2
                End If
            End If
        End If
    End If
    Application.EnableEvents = True
End Sub

3 Натисніть кнопку інший + Q клавіші, щоб закрити Microsoft Visual Basic для додатків вікна.

Тепер ви можете вибрати кілька елементів зі спадного списку на поточному аркуші.

Припустимо, що ви випадково вибрали елемент і вам потрібно видалити його, не очищаючи всю комірку та починаючи спочатку. Наступний код VBA 2 може зробити вам послугу.

Код VBA 2: дозволити багаторазовий вибір у розкривному списку без дублікатів (видаліть наявні елементи, вибравши їх знову)

Private Sub Worksheet_Change(ByVal Target As Range)
    'Updated by Extendoffice 2023/01/11
    'Updated by Ken Gardner 2022/07/11
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    Dim semiColonCnt As Integer
    Dim xType As Integer
    If Target.Count > 1 Then Exit Sub
    On Error Resume Next
    
    xType = 0
    xType = Target.Validation.Type
    If xType = 3 Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        xValue2 = Target.Value
        Application.Undo
        xValue1 = Target.Value
        Target.Value = xValue2
        If xValue1 <> "" Then
            If xValue2 <> "" Then
                If xValue1 = xValue2 Or xValue1 = xValue2 & ";" Or xValue1 = xValue2 & "; " Then ' leave the value if only one in list
                    xValue1 = Replace(xValue1, "; ", "")
                    xValue1 = Replace(xValue1, ";", "")
                    Target.Value = xValue1
                ElseIf InStr(1, xValue1, "; " & xValue2) Then
                    xValue1 = Replace(xValue1, xValue2, "") ' removes existing value from the list on repeat selection
                    Target.Value = xValue1
                ElseIf InStr(1, xValue1, xValue2 & ";") Then
                    xValue1 = Replace(xValue1, xValue2, "")
                    Target.Value = xValue1
                Else
                    Target.Value = xValue1 & "; " & xValue2
                End If
                Target.Value = Replace(Target.Value, ";;", ";")
                Target.Value = Replace(Target.Value, "; ;", ";")
                If Target.Value <> "" Then
                    If Right(Target.Value, 2) = "; " Then
                        Target.Value = Left(Target.Value, Len(Target.Value) - 2)
                    End If
                End If
                If InStr(1, Target.Value, "; ") = 1 Then ' check for ; as first character and remove it
                    Target.Value = Replace(Target.Value, "; ", "", 1, 1)
                End If
                If InStr(1, Target.Value, ";") = 1 Then
                    Target.Value = Replace(Target.Value, ";", "", 1, 1)
                End If
                semiColonCnt = 0
                For i = 1 To Len(Target.Value)
                    If InStr(i, Target.Value, ";") Then
                        semiColonCnt = semiColonCnt + 1
                    End If
                Next i
                If semiColonCnt = 1 Then ' remove ; if last character
                    Target.Value = Replace(Target.Value, "; ", "")
                    Target.Value = Replace(Target.Value, ";", "")
                End If
            End If
        End If
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

примітки:

Легко створюйте випадаючий список за допомогою декількох виділень за допомогою дивовижного інструменту

Тут ми настійно рекомендуємо Вибірний список, що випадає особливість Kutools for Excel для вас. За допомогою цієї функції ви можете легко вибрати кілька елементів зі спадного списку у зазначеному діапазоні, поточному аркуші, поточній книзі або всім відкритим книгам, як вам потрібно.

1. клацання Кутулс > Випадаючий список > Вибірний список, що випадає > Налаштування. Дивіться знімок екрана:

2 В Виберіть кілька налаштувань спадного списку діалогове вікно, будь ласка, налаштуйте наступним чином.

• 2.1) Вкажіть сферу застосування в Застосувати до розділ. У цьому випадку я вибираю Поточний аркуш від Вказаний обсяг випадаючий список;
• 2.2) У Напрямок тексту розділ, виберіть напрямок тексту відповідно до ваших потреб;
• 2.3) У сепаратор поле, введіть роздільник, який ви будете використовувати для розділення кількох значень;
• 2.4) Перевірте Не додавати дублікати коробка в опції розділ, якщо ви не хочете робити дублікати в комірках спадного списку;
• 2.5) Клацніть на OK кнопку. Дивіться знімок екрана:

3. Клацніть, будь ласка Кутулс > Випадаючий список > Вибірний список, що випадає щоб увімкнути цю функцію.

Тепер ви можете вибрати кілька елементів зі спадного списку на поточному аркуші або будь-яку область, яку ви вказали на кроці 2.

Статті по темі:

---

Найкращі інструменти для підвищення продуктивності офісу

Kutools for Excel Вирішує більшість ваших проблем і підвищує вашу продуктивність на 80%

• Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
• Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
• Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
• Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
• Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
• Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
• Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
• Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
• Понад 300 потужних функцій. Підтримує Office / Excel 2007-2021 і 365. Підтримує всі мови. Легке розгортання на вашому підприємстві чи в організації. 30-денна безкоштовна пробна версія повних функцій. 60-денна гарантія повернення грошей.

Детальніше ... Завантажити безкоштовно... Придбати ... 

---

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

• Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
• Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
• Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!

Детальніше ... Завантажити безкоштовно... Придбати ...