Up till now, we know how to create drop-down lists in Excel. Today I will be showing a little more trick, how to create a multi select listbox in Excel.
Download Practice Workbook
How to Create Multi Select Listbox in Excel
Here we’ve got a data set with the seven days of a week.
Our objective today is to create a multi select listbox that will select multiple days from the Weekdays as Working Days.
Step 1: Creating a Drop-Down List Using Data Validation
First of all, we will create a drop-down list that can select single days.
- Go to Formulas>Define Name>Define Name in Excel Toolbar.
- Click on Define Name. You will get a dialogue box called New Name.
In the Name box, enter a name according to your wish. I am entering “Weekdays”.
And in the Scope box, select Workbook.
Then in the Refers to box, enter the cell reference of the Weekdays.
- Click on OK. Then select the cell where you want to enter the drop-down list and go to Formula>Data Validation>Data Validation in Excel Toolbar.
- Click on Data Validation. You will get the Data Validation dialogue box.
From the Allow option, select List.
And in the Source option, enter the name of the named range (Weekdays in this example)
- Then click OK. You will find a drop-down list created in your selected cell.
Step 2: Enabling It to Select Multiple Items from a Listbox Using VBA
Now we want to enable the drop-down list to generate a listbox and accept multiple selections.
- Press Alt + F11 on your keyboard. It will open the VBA window. In the left panel under the VBAProject folder, click on the worksheet name (Sheet1 in this case).
- Enter this VBA code here.
Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String Dim strList As String On Error Resume Next Application.EnableEvents = False Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitHandler If rngDV Is Nothing Then GoTo exitHandler If Not Intersect(Target, rngDV) Is Nothing Then If Target.Validation.Type = 3 Then strList = Target.Validation.Formula1 strList = Right(strList, Len(strList) - 1) strDVList = strList frmDVList.Show End If End If exitHandler: Application.EnableEvents = True End Sub
- Save the file as Excel Macro Enabled Workbook.
- Now come back to your worksheet. You can select multiple items from the listbox now.
Using this method, you can create a multi select listbox in Excel. Do you have any questions? Feel free to ask us.