How to Create Multi Select Listbox in Excel

Excel Multi Select Listbox

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.

Data Set to Create Multi Select Listbox

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.

Define Name Tool 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.

New Name Dialogue Box in Excel

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

Data Validation Tool 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)

Data Validation Dialogue Box in Excel

  • Then click OK. You will find a drop-down list created in your selected cell.

Drop Down List in Excel


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

VBA Window Opened in Excel

  • Enter this VBA code here.

Code:

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

VBA Code to Create Multi Select Listbox

  • Save the file as Excel Macro Enabled Workbook.

Saving Macro Enabled File in Excel

  • Now come back to your worksheet. You can select multiple items from the listbox now.

Excel Multi Select Listbox


Conclusion

Using this method, you can create a multi select listbox in Excel. Do you have any questions? Feel free to ask us.

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

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo