Един от моите колеги ме попита дали е възможно да се направят няколко избора в падащ списък в Excel.
Когато създавате падащ списък, можете да направите само един избор. Ако изберете друг елемент, първият се заменя с новия избор.
Той искаше да направи множество селекции от едно и също падащо меню по такъв начин, че селекциите да се добавят към вече съществуващата стойност в клетката.
Нещо, както е показано по -долу на снимката:
Няма начин да направите това с вградени функции на Excel.
Единственият начин е да използвате VBA код, който се изпълнява всеки път, когато правите избор и добавя избраната стойност към съществуващата стойност.
Гледайте видео - Как да изберете няколко елемента от падащия списък на Excel
Как да направите няколко избора в падащ списък
В този урок ще ви покажа как да направите няколко избора в падащ списък на Excel (с повторение и без повторение).
Това е един от най -популярните уроци по Excel на този сайт. Тъй като получавам много подобни въпроси, реших да създам раздел с често задавани въпроси в края на този урок. Така че, ако имате въпроси, след като прочетете това, моля, първо разгледайте раздела с често задавани въпроси.Създаването на падащ списък, който позволява множество избори, има две части:
- Създаване на падащ списък.
- Добавяне на VBA код към задния край.
Създаване на падащ списък в Excel
Ето стъпките за създаване на падащ списък в Excel:
- Изберете клетката или диапазона от клетки, където искате да се появи падащият списък (C2 в този пример).
- Отидете на Данни -> Инструменти за данни -> Проверка на данни.
- В диалоговия прозорец Проверка на данни в раздела с настройки изберете „Списък“ като критерии за валидиране.
- В поле Източник изберете клетките, които имат елементите, които искате в падащото меню.
- Щракнете върху OK.
Сега клетка C2 има падащ списък, който показва имената на елементите в A2: A6.
Към момента имаме падащ списък, където можете да избирате един елемент наведнъж (както е показано по-долу).
За да активираме това падащо меню, за да можем да правим множество избори, трябва да добавим кода VBA в задната част.
Следващите два раздела на този урок ще ви дадат VBA кода, за да разрешите множество избори в падащия списък (със и без повторение).
VBA код, позволяващ множество селекции в падащ списък (с повторение)
По-долу е кодът на Excel VBA, който ще ни позволи да изберем повече от един елемент от падащия списък (позволяващ повторения при избор):
Private Sub Worksheet_Change (ByVal Target As Range) 'Код от Sumit Bansal от https://trumpexcel.com' За да направите многократни селекции в падащ списък в Excel Dim Oldvalue As String Dim Newvalue As String On Error GoTo Exitsub If Target.Address = "$ C $ 2" Тогава ако Target.SpecialCells (xlCellTypeAllValidation) не е нищо, тогава GoTo Exitsub Друго: Ако Target.Value = "" Тогава GoTo Exitsub Друго приложение.EnableEvents = False Newvalue = Target.Value Application.Undo Oldvalue = Target.Value If Oldvalue = "" Тогава Target.Value = Newvalue Else Target.Value = Oldvalue & "," & Newvalue End If End Ако End End Ако Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub
Сега трябва да поставите този код в модул във VB Editor (както е показано по -долу в раздела „Къде да поставите VBA кода“).
Когато поставите този код в бекенда (обхванат по -късно в този урок), той ще ви позволи да направите няколко избора в падащото меню (както е показано по -долу).
Имайте предвид, че ако изберете елемент повече от веднъж, той ще бъде въведен отново (разрешено е повторение).
Опитайте сами … Изтеглете примерния файл
VBA код, позволяващ множество селекции в падащ списък (без повторение)
Много хора питат кода за избор на няколко елемента от падащ списък без повторение.
Ето кода, който ще гарантира, че даден елемент може да бъде избран само веднъж, така че да няма повторения:
Private Sub Worksheet_Change (ByVal Target As Range) 'Код от Sumit Bansal от https://trumpexcel.com' За разрешаване на множество селекции в падащ списък в Excel (без повторение) Dim Oldvalue As String Dim Newvalue As String Application.EnableEvents = True On Error GoTo Exitsub If Target.Address = "$ C $ 2" Тогава ако Target.SpecialCells (xlCellTypeAllValidation) не е нищо, тогава GoTo Exitsub Else: If Target.Value = "" Тогава GoTo Exitsub Else Application.EnableEvents = False Newvalue = Targe. Стойност 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 Editor (както е показано в следващия раздел на този урок).
Този код ще ви позволи да изберете няколко елемента от падащия списък. Ще можете обаче да изберете елемент само веднъж. Ако опитате и го изберете отново, нищо няма да се случи (както е показано по -долу).
Опитайте сами … Изтеглете примерния файл
Къде да поставите VBA кода
Преди да започнете да използвате този код в Excel, трябва да го поставите в задната част, така че да се задейства, когато има някаква промяна в падащото меню.
Следвайте стъпките по -долу, за да поставите VBA кода в задната част на Excel:
- Отидете в раздела Developer и кликнете върху Visual Basic (можете да използвате и клавишната комбинация - Alt + F11). Това ще отвори редактора на Visual Basic.
- Вляво трябва да има прозорец на Project Explorer (ако не е там, използвайте Control + R, за да стане видим).
- Щракнете двукратно върху Име на работен лист (в левия прозорец), където се намира падащият списък. Това отваря прозореца с код за този работен лист.
- В прозореца на кода копирайте и поставете горния код.
- Затворете редактора на VB.
Сега, когато се върнете към падащото меню и направите избор, това ще ви позволи да направите няколко избора (както е показано по-долу):
Опитайте сами … Изтеглете примерния файл
Забележка: Тъй като използваме VBA код, за да направим това, трябва да запишете работната книга с разширение .xls или .xlsm.
Често задавани въпроси (често задавани въпроси)
Създадох този раздел, за да отговоря на някои от най -често задаваните въпроси относно този урок и VBA кода. Ако имате някакви въпроси, моля да преминете първо през този списък с заявки.
В: Във VBA кода функционалността е само за клетка C2. Как да го получа за други клетки? Отговор: За да получите това падащо меню за многократен избор в други клетки, трябва да промените кода VBA в задната част. Да предположим, че искате да получите това за C2, C3 и C4, трябва да замените следния ред в кода: If Target.Address = "$ C $ 2" След това с този ред: If Target.Address = "$ C $ 2" Или Target.Address = "$ C $ 3" Или Target.Address = "$ C $ 4" След това
В: Трябва да създам множество падащи менюта в цялата колона „C“. Как мога да получа това за всички клетки в колоните с функционалност за многократен избор? Отговор: За да активирате множество селекции в падащи менюта в цяла колона, заменете следния ред в кода: Ако Target.Address = "$ C $ 2" След това с този ред: Ако Target.Column = 3 Тогава На подобни редове, ако искате тази функционалност в колони C и D, използвайте долния ред: Ако Target.Column = 3 или Target.Column = 4 Тогава
В: Трябва да създам няколко падащи менюта подред. Как мога да направя това? Отговор: Ако трябва да създадете падащи списъци с множество селекции подред (да речем втори ред), трябва да замените долния ред код: If 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
