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

Get FREE Advanced Excel Exercises with Solutions!

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.


Download Practice Workbook

Get the sample file to practice.


Step-by-Step Procedures to Create Multi Select ListBox in Excel

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: Create 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.

Create Excel Table from Dataset

  • 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.

Read more: How to Create Dependent Drop Down List in Excel


Step 2: Name 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.

Name Dataset List from Name Manager

  • 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: Create 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.

Create Drop Down List with Data Validation

  • 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: Insert 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.

Insert VBA Code to Validated Worksheet

  • 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

Thanks to Contextures for providing the codes.


Step 5: Create UserForm with Listbox & Buttons

At this stage, we will create an 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.

Create UserForm with Listbox & Buttons

  • 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: Change Properties Settings

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.

Change Properties Settings

  • 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: Apply 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.

Apply VBA Code to UserForm

  • 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.

Thanks to Contextures for helping with the codes.


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.

Excel Multi Select ListBox

  • Then, press OK.
  • Finally, you have successfully multi-selected from the ListBox and each name is separated by a Comma (,).


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.

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. Follow ExcelDemy for more articles like this.


Further Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

2 Comments
  1. 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.

      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim rngDV As Range
      Dim oldVal As String
      Dim newVal As String
      If Target.Count > 1 Then GoTo exitHandler
      
      On Error Resume Next
      Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
      On Error GoTo exitHandler
      
      If rngDV Is Nothing Then GoTo exitHandler
      
      If Intersect(Target, rngDV) Is Nothing Then
      Else
        Application.EnableEvents = False
        newVal = Target.Value
        Application.Undo
        oldVal = Target.Value
        Target.Value = newVal
        If Target.Column = 3 Then
          If oldVal = "" Then
            Else
            If newVal = "" Then
            Else
            Target.Value = oldVal & ", " & newVal
      
            End If
          End If
        End If
      End If
      
      exitHandler:
        Application.EnableEvents = True
      End Sub

      1

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo