Jeden z moich kolegów zapytał mnie, czy w Excelu można dokonać wielu wyborów z rozwijanej listy.
Tworząc listę rozwijaną, możesz dokonać tylko jednego wyboru. Jeśli wybierzesz inny element, pierwszy zostanie zastąpiony nowym wyborem.
Chciał dokonać wielu selekcji z tego samego menu rozwijanego w taki sposób, aby selekcje zostały dodane do już obecnej wartości w komórce.
Coś jak pokazano poniżej na pic:
Nie można tego zrobić za pomocą wbudowanych funkcji programu Excel.
Jedynym sposobem jest użycie kodu VBA, który jest uruchamiany za każdym razem, gdy dokonujesz wyboru i dodaje wybraną wartość do istniejącej wartości.
Obejrzyj wideo - jak wybrać wiele elementów z listy rozwijanej programu Excel
Jak dokonać wielu wyborów na liście rozwijanej
W tym samouczku pokażę, jak dokonać wielu wyborów na liście rozwijanej Excela (z powtórzeniami i bez powtórzeń).
Był to jeden z najpopularniejszych samouczków programu Excel w tej witrynie. Ponieważ otrzymuję wiele podobnych pytań, postanowiłem utworzyć sekcję FAQ na końcu tego samouczka. Więc jeśli masz jakieś pytania po przeczytaniu tego, najpierw sprawdź sekcję FAQ.Tworzenie listy rozwijanej umożliwiającej wielokrotny wybór składa się z dwóch części:
- Tworzenie listy rozwijanej.
- Dodanie kodu VBA do zaplecza.
Tworzenie listy rozwijanej w programie Excel
Oto kroki, aby utworzyć listę rozwijaną w programie Excel:
- Zaznacz komórkę lub zakres komórek, w których ma się pojawić lista rozwijana (w tym przykładzie C2).
- Przejdź do Dane -> Narzędzia danych -> Walidacja danych.
- W oknie dialogowym Walidacja danych, w zakładce ustawień, wybierz „Lista” jako Kryteria weryfikacji.
- W polu Źródło wybierz komórki zawierające elementy, które chcesz wyświetlić na liście rozwijanej.
- Kliknij OK.
Teraz komórka C2 ma listę rozwijaną, która pokazuje nazwy elementów w A2: A6.
W tej chwili mamy rozwijaną listę, z której możesz wybrać jeden element na raz (jak pokazano poniżej).
Aby włączyć tę listę rozwijaną, aby umożliwić nam dokonywanie wielu wyborów, musimy dodać kod VBA na zapleczu.
Następne dwie sekcje tego samouczka zawierają kod VBA, który umożliwia wielokrotne wybieranie z listy rozwijanej (z powtórzeniami i bez).
Kod VBA, aby umożliwić wiele wyborów na liście rozwijanej (z powtórzeniem)
Poniżej znajduje się kod Excel VBA, który pozwoli nam wybrać więcej niż jedną pozycję z listy rozwijanej (umożliwiając powtórki w zaznaczeniu):
Private Sub Worksheet_Change (ByVal Target As Range) 'Kod Sumit Bansal z https://trumpexcel.com ' Aby dokonać wielu wyborów na liście rozwijanej w programie Excel Dim Oldvalue As String Dim Newvalue As String On Error GoTo Exitsub If Target.Address = "$ C$2" Then If Target.SpecialCells(xlCellTypeAllValidation) to nic Then GoTo Exitsub Else: If Target.Value = "" Then GoTo Exitsub Else Application.EnableEvents = False Newvalue = Target.Value Application.Undo Oldvalue = Target.Value If Oldvalue = "" Then Target.Value = Newvalue Else Target.Value = Oldvalue & ", " & Newvalue End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub
Teraz musisz umieścić ten kod w module w edytorze VB (jak pokazano poniżej w sekcji „Gdzie umieścić kod VBA”).
Po umieszczeniu tego kodu w backendzie (opisanym w dalszej części tego samouczka), umożliwi to dokonanie wielu wyborów z listy rozwijanej (jak pokazano poniżej).
Pamiętaj, że jeśli wybierzesz element więcej niż raz, zostanie on wprowadzony ponownie (powtórzenie jest dozwolone).
Spróbuj sam… Pobierz przykładowy plik
Kod VBA umożliwiający wiele wyborów na liście rozwijanej (bez powtórzeń)
Wiele osób pytało o kod do wybierania wielu elementów z rozwijanej listy bez powtórzeń.
Oto kod, który sprawi, że element będzie można wybrać tylko raz, aby nie było powtórzeń:
Private Sub Worksheet_Change(ByVal Target As Range) 'Kod Sumit Bansal z https://trumpexcel.com ' Aby zezwolić na wiele wyborów na liście rozwijanej w programie Excel (bez powtórzeń) Dim Oldvalue As String Dim Newvalue As String Application.EnableEvents = True On Error GoTo Exitsub If Target.Address = "$C$2" Then If Target.SpecialCells(xlCellTypeAllValidation) to nic Then GoTo Exitsub Else: If Target.Value = "" Then GoTo Exitsub Else Application.EnableEvents = False Newvalue = Target. Wartość Application.Undo Oldvalue = Target.Value If Oldvalue = "" Then Target.Value = Newvalue Else If InStr(1, Oldvalue, Newvalue) = 0 Then Target.Value = Oldvalue & ", " & Newvalue Else: Target.Value = Oldvalue End If End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub
Teraz musisz umieścić ten kod w module w edytorze VB (jak pokazano w następnej sekcji tego samouczka).
Ten kod pozwoli Ci wybrać wiele pozycji z listy rozwijanej. Jednak przedmiot będzie można wybrać tylko raz. Jeśli spróbujesz i wybierzesz go ponownie, nic się nie stanie (jak pokazano poniżej).
Spróbuj sam… Pobierz przykładowy plik
Gdzie umieścić kod VBA
Zanim zaczniesz używać tego kodu w programie Excel, musisz umieścić go na zapleczu, tak aby był uruchamiany za każdym razem, gdy nastąpi jakakolwiek zmiana w liście rozwijanej.
Wykonaj poniższe kroki, aby umieścić kod VBA w zapleczu programu Excel:
- Przejdź do zakładki Deweloper i kliknij Visual Basic (możesz również użyć skrótu klawiaturowego - Alt + F11). Spowoduje to otwarcie Edytora Visual Basic.
- Po lewej stronie powinno znajdować się okienko Project Explorer (jeśli go tam nie ma, użyj Control + R, aby było widoczne).
- Kliknij dwukrotnie nazwę arkusza roboczego (w lewym okienku), gdzie znajduje się lista rozwijana. Spowoduje to otwarcie okna kodu dla tego arkusza roboczego.
- W oknie kodu skopiuj i wklej powyższy kod.
- Zamknij edytor VB.
Teraz, gdy wrócisz do listy rozwijanej i dokonasz wyborów, umożliwi to dokonanie wielu wyborów (jak pokazano poniżej):
Spróbuj sam… Pobierz przykładowy plik
Notatka: Ponieważ używamy kodu VBA, aby to zrobić, musisz zapisać skoroszyt z rozszerzeniem .xls lub .xlsm.
Często zadawane pytania (FAQ)
Stworzyłem tę sekcję, aby odpowiedzieć na niektóre z najczęściej zadawanych pytań dotyczących tego samouczka i kodu VBA. Jeśli masz jakieś pytania, proszę najpierw przejrzeć tę listę zapytań.
P: W kodzie VBA funkcja dotyczy tylko komórki C2. Jak mogę to zdobyć dla innych komórek? Odp.: Aby uzyskać menu wielokrotnego wyboru w innych komórkach, musisz zmodyfikować kod VBA w zapleczu. Załóżmy, że chcesz uzyskać to dla C2, C3 i C4, musisz zastąpić następujący wiersz w kodzie: If Target.Address = "$C$2" Następnie tym wierszem: If Target.Address = "$C$2" Lub Target.Address = "$C$3" Lub Target.Address = "$C$4" Wtedy
P: Muszę utworzyć wiele list rozwijanych w całej kolumnie „C”. Jak uzyskać to dla wszystkich komórek w kolumnach z funkcją wielokrotnego wyboru? Odp.: Aby włączyć wielokrotne zaznaczenia w listach rozwijanych w całej kolumnie, zastąp następujący wiersz w kodzie: If Target.Address = "$C$2" Następnie tym wierszem: If Target.Column = 3 Then W podobnych wierszach, jeśli chcesz tę funkcjonalność w kolumnie C i D, użyj poniższego wiersza: If Target.Column = 3 lub Target.Column = 4 Then
P: Muszę utworzyć wiele list rozwijanych z rzędu. Jak mogę to zrobić? Odp: Jeśli chcesz utworzyć listy rozwijane z wieloma zaznaczeniami w wierszu (powiedzmy, że drugi wiersz), musisz zamienić poniższy wiersz kodu: If Target.Address = "$C$2" Następnie tym wierszem: If Target.Row = 2 Then Podobnie, jeśli chcesz, aby to działało dla wielu wierszy (powiedzmy drugiego i trzeciego wiersza), użyj zamiast tego poniższego wiersza kodu: If Target.Row = 2 lub Target.Row = 3 Then
P: W tej chwili wielokrotne zaznaczenia są oddzielone przecinkiem. Jak mogę to zmienić, aby oddzielić je spacją (lub dowolnym innym separatorem). Odp: Aby oddzielić je separatorem innym niż przecinek, musisz zastąpić następujący wiersz kodu VBA: Target.Value = Oldvalue & ", " & Newvalue tym wierszem kodu VBA: Target.Value = Oldvalue & " " & Nowawartość Podobnie, jeśli chcesz zmienić przecinek na inny znak, taki jak |, możesz użyć następującego wiersza kodu: Target.Value = Oldvalue & "| " & Newvalue
P: Czy mogę uzyskać każdy wybór w osobnym wierszu w tej samej komórce? Odp: Tak, możesz. Aby to uzyskać, musisz zastąpić poniższy wiersz kodu VBA: Target.Value = Oldvalue & ", " & Newvalue tym wierszem kodu: Target.Value = Oldvalue & vbNewLine & Newvalue vbNewLine wstawia nowy wiersz w tej samej komórce . Tak więc za każdym razem, gdy dokonasz wyboru z listy rozwijanej, zostanie on wstawiony w nowej linii.
P: Czy mogę sprawić, by funkcja wielokrotnego wyboru działała w chronionym arkuszu? Odp: Tak, możesz. Aby to zrobić, musisz zrobić dwie rzeczy: Dodaj następujący wiersz w kodzie (zaraz po instrukcji DIM): Me.Protect UserInterfaceOnly:=True Po drugie, musisz upewnić się, że komórki – które mają listę rozwijaną z funkcją wielokrotnego zaznaczania – nie są zablokowane, gdy chronisz cały arkusz. Oto samouczek, jak to zrobić: Zablokuj komórki w programie Excel
