How To Create Drop Down List With Multiple Selections Or Values In ...

Trang chủ » Visual Basic Excel Drop Down List » How To Create Drop Down List With Multiple Selections Or Values In ...

Có thể bạn quan tâm

Using VBA Code

To allow multiple selections in a drop-down list, you can use "Visual Basic for Applications" (VBA) in Excel. The script can modify the behavior of a drop-down list to make it a multiple-choice list. Please do as follows.

Step 1: Open the Sheet (Code) editor
  1. Open the worksheet that contains the drop-down list for which you want to enable multiple selection.
  2. Right click the sheet tab and select "View Code" from the context menu. A screenshot of the View Code option in the context menu of a sheet tab in Excel
Step 2: Use VBA code

Now copy the following VBA code and paste it to the opening sheet (Code) window.

VBA code: Enable multiple selections in Excel drop-down list.

Private Sub Worksheet_Change(ByVal Target As Range) 'Updated by Extendoffice 20240118 Dim xRng As Range Dim xValue1 As String Dim xValue2 As String Dim delimiter As String Dim TargetRange As Range Set TargetRange = Me.UsedRange ' Users can change target range here delimiter = ", " ' Users can change the delimiter here If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub On Error Resume Next Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation) If xRng Is Nothing Then Exit Sub Application.EnableEvents = False xValue2 = Target.Value Application.Undo xValue1 = Target.Value Target.Value = xValue2 If xValue1 <> "" And xValue2 <> "" Then If Not (xValue1 = xValue2 Or _ InStr(1, xValue1, delimiter & xValue2) > 0 Or _ InStr(1, xValue1, xValue2 & delimiter) > 0) Then Target.Value = xValue1 & delimiter & xValue2 Else Target.Value = xValue1 End If End If Application.EnableEvents = True On Error GoTo 0 End Sub

A screenshot of the VBA code pasted into the Excel VBA editor

Result

When you return to the worksheet, the drop-down list will enable you to choose multiple options, see the demo below:

A screenshot of the animated demo showing multiple selections in an Excel drop-down list

Notes:The above VBA code:
  • Applies to all data validation drop-down lists in the current worksheet, both existing and those created in the future.
  • Prevents you from picking the same item more than once in each drop-down list.
  • Uses comma as the separator for the selected items. To use other delimiters, please view this section to change the separator.

Từ khóa » Visual Basic Excel Drop Down List