How to Create Multi Select ListBox in Excel (With Easy Steps)

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.

Related Articles

• How to Populate Excel VBA ListBox Using RowSource
• How to Sort ListBox with VBA in Excel
• Excel Button to Print Specific Sheets