When we work on a large dataset, often it gets difficult to fetch any selective value from it. Moreover, you cannot choose more than multiple pieces of information simultaneously. In this case, ListBox is a very helpful solution in Excel. But the process of creating this ListBox is a bit tricky. Therefore, in this article, we will learn how to create a multi-select ListBox in Excel with some simple steps.
To make the process easier, we have divided it into 8 steps for better understanding. So, without further delay, let’s hop into the steps below to see how we can create a multi-select ListBox in Excel.
Step 1: Creating Excel Table from Dataset
Initially, we need to prepare a sample dataset and convert it into a table. To do this, follow the steps below.
- First, create a dataset with the information of 10 City Names and their Total Population of the USA till 1, July in the Cell range B5:C14.
- Now, click on any cell of the dataset and choose Table from the Insert tab.
- Then, you will see the Create Table window which automatically selects the cell range to create a table.
- In this window, mark checked the My table has headers box and press OK.
- As a result, you will see the dataset is converted to a table.
- Along with it, you can find the table in the Table Name box under the Table Design tab
- You can change the table name according to your preference.
Step 2: Naming Dataset List from Name Manager
Now, we will name each category of cell range from the table. For this, go through the steps.
- First, select any cell from Column B in the table.
- Then, go to the Formulas tab and select Define Name.
- Following this, you will see the New Name dialogue box.
- In this dialogue box, provide any name as per the selected column header in the Name box.
- Next, click on the Refers to box in the same window.
- Then, put the cursor over the header and it will show a black arrow.
- Afterward, press left-click to select the Cell range B5:B14.
- As a result, you will see the list of names along with the table name in the Refers to box and press OK.
- Follow the same procedure, for the Cell range C5:C14 as well.
- Finally, you will see the names in the Name Box in the upper left corner of the workbook.
Step 3: Creating Drop Down List with Data Validation
At this stage, we will create a drop-down list out of the named ranges with data validation. This is the essential part of creating a ListBox. We will create this in another worksheet in the workbook. But you can do this in the same worksheet as well. Let’s see the process below.
- In the beginning, select some cells from the table where you want to apply Data Validation.
- Then, go to the Data tab and choose Data Validation in the Data Tools section.
- Next, in the Settings tab, choose List in the Allow box.
- Also, mark checked the Ignore blank and In-cell dropdown boxes.
- Afterward, click on the Source box in this window and press F3 on your keyboard.
- As a result, you will see the Paste Name dialogue box with the name list.
- Here, choose CityNames from the list and press OK.
- Then, you will see the first list’s name showing in the source box.
- Lastly, press OK and apply the same process for the second name list.
- Finally, you will see that Data Validation is activated on the selected cells.
Step 4: Inserting VBA Code to Validated Worksheet
Now comes the crucial part of inserting VBA code to create a ListBox. Following is the process for this.
- First, right-click on the validated worksheet and select View Code from the Context Menu.
- Then, insert this code on the page.
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
- Next, go to the Insert tab and select Module.
- At this point, the workbook name must be selected in the Project Object window.
- Then, rename the module as modSettings and insert this code.
Option Explicit
Global strDVList As String
Step 5: Creating UserForm with Listbox & Buttons
At this stage, we will create a UserForm for the workbook along with a ListBox and some Command Buttons. To do this, follow the process below.
- First, select the workbook in the Project-VBAProject window in the Visual Basic editor.
- Then, go to the Insert tab and select UserForm.
- As a result, you will get the UserForm interface like this.
- Along with this, you will also get the Toolbox window.
- From here, drag ListBox to the UserForm.
- Then, the ListBox will look like this. You can adjust the size by dragging the edges of the box.
- Next, drag the CommanButton twice to UserForm as well to create 2 buttons for operation.
- Lastly, the final output looks like this.
Step 6: Changing Settings of Properties
In this stage, we will make some changes in the properties of each component of the ListBox.
- In the beginning, press F4 on the Visual Basic editor to open the Properties Window.
- Then, select the UserForm and change the Name and Caption of it like this.
- Next, select ListBox and change the Name according to your preference.
- Additionally, change the type of ListStyle, MultiSelect and SpecialEffect as per the image below.
- Now, choose the first command button and make the following changes in the properties.
- In addition to that, edit the properties of the second command button as well.
Step 7: Applying VBA Code to UserForm
At this stage, we will apply VBA codes to each of the components of the UserForm. Let’s see how it works.
- First, select UserForm and go to the View tab to select Code.
- Then, insert this code on the blank page. It will automatically run when the UserForm is opened.
Private Sub UserForm_Initialize()
Me.lstDV.RowSource = strDVList
End Sub
- After this, go back to the UserForm interface by clicking on Object on the View tab.
- Now, follow the process to insert this code for the OK button.
Private Sub cmdOK_Click()
Dim strSelItems As String
Dim lCountList As Long
Dim strSep As String
Dim strAdd As String
Dim bDup As Boolean
On Error Resume Next
strSep = ", "
With Me.lstDV
For lCountList = 0 To .ListCount - 1
If .Selected(lCountList) Then
strAdd = .List(lCountList)
Else
strAdd = ""
End If
If strSelItems = "" Then
strSelItems = strAdd
Else
If strAdd <> "" Then
strSelItems = strSelItems _
& strSep & strAdd
End If
End If
Next lCountList
End With
With ActiveCell
If .Value <> "" Then
.Value = ActiveCell.Value _
& strSep & strSelItems
Else
.Value = strSelItems
End If
End With
Unload Me
End Sub
- Along with this, type this code for the Close button using the same process.
Private Sub cmdClose_Click()
Unload Me
End Sub
- Lastly, press Ctrl + S to save it and close the window.
Step 8: Multi Select from ListBox
Finally, we have successfully created a ListBox for multiple selections. To check if the code is working or not, simply go through these steps.
- First, select Cell B5 where we applied Data Validation.
- Just right after that, a ListBox will pop-up commanding Select Item from List.
- In this window, choose more than one name from the list.
- Then, press OK.
- Finally, you have successfully multi-selected from the ListBox and each name is separated by a Comma (,).
Read More: Create ListBox for Multiple Columns in Excel VBA
Things to Remember
- Named ranges will not create a Data Validation rule if they are entered as a cell reference or with delimiters.
- The Global variable is applied for both UserForm and Worksheet VBA code. Any active cell name initially passes the code strDVList to a temporary range and then is used as a RowSource for the ListBox when a user opens the UserForm.
- You can combine multiple ranges in a single name for ease of selection.
Download Practice Workbook
Get the sample file to practice.
Conclusion
That’s all for today. I hope these long but simple steps on how to create multi select ListBox in Excel have made the topic a little bit easier for you. Let us know your feedback in the comment box.
I’m receiving a “Compile error: Variable not defined” and it’s highlighting “strDVList =”
Hello Julie,
You can try out the following code. I think it will work for you. Just make sure to change the number in Target.Column = 3 according to your column number of data validation.