ListBox MultiSelect VBA

Selecting multiple values from List box using VBA in Microsoft Excel

In this article, we will learn how to create a list box in which we can select multiple names and can get the required data from the main data.

Lets understand with the simple exercise:-

We have taken data in which we have email id detail for every employee.

Follow the steps given below to create the list box which provides option of selection of multiple choices using VBA code:-

Inserting List Box

For inserting List Box on Excel sheet, Go to Developer tab > Click on Insert > and then Click at List box button under ActiveX Control

On Developer tab, Click on Design Mode for activating the design, then click on Properties in the Developer tab, Properties dialog box will appear.

Select the List box, then go to Properties dialog box. Click to Categorized tab. Under the Misc heading, Go to ListFillRange property and assign the range which contains the values for the List Box.

Under the Behavior heading, change the Multiselect property to 1-fmMultiSelectMulti

Now, insert two command button on Excel sheet, Go to Developer tab > Click on Insert > and then Click at Button icon under Form Control

Right click the Button and select the Edit text Property and rename the two button as Unselect Names and Submit.

Press key Alt+F11 to open Visual basic Editor

Add new module and insert the below code in the module.

Coding

Above code contain two procedure GettingSelectedItems and UnselectedItems. Assign procedure GettingSelectedItems to Submit button and procedure UnselectedItems to Unselect Names button.

After assigning the procedure, disable the design mode by clicking on design mode button on Developer tab.

Logic explanation

In this example, our goal is to fetch the email id of name selected by the user in the List Box to L column.

User can select multiple names in the List Box. After selecting the names, user will press the submit button and email id corresponding to the selected name in the List Box will appear in the column L on the Excel sheet.

If user wants to unselect all the selected names in the List Box, user can click on Unselect Names button.

To achieve above goal, we have used excel formula and procedure.

Procedure GettingSelectedItems is written for getting the selected name from the List Box to range K10 to K23.

For getting the email id for selected name, we have used following Excel formula:-

=IFERROR[VLOOKUP[K10,$A$10:$B$23,2,0],""]

Insert the above formula in cell L10 and copy and paste this formula in range L10 to L23.

This formula will provide the email id by looking up the selected name in range A10 to B23.

We have hidden the values in the range K10 to K23 by using same color for font and background of the cell.

Please follow below for the code

Option Explicit Sub GettingSelectedItems[] 'Getting selected items in ListBox1 Application.ScreenUpdating = False 'Declaring varialbes Dim ValueSelected As String, i, r As Integer 'deleting selected values Range["K10:K23"].Select Selection.ClearContents 'Defining listbox1 of sheet TestDialog With Sheets["TestDialog"].ListBox1 r = 0 'Using For loop for looping through all the items in List Box For i = 0 To .ListCount - 1 'Using .selected for selecting only those value which is selected by user in listbox If .Selected[i] Then 'Assigning selected value of List Box to Column K on the sheet Cells[r + 10, 11].Value = .List[i] r = r + 1 End If Next i End With Range["L10"].Select End Sub Sub UnselectedItems[] 'Unselecting all the items in the list box Application.ScreenUpdating = False 'Declaring varialbes Dim ValueSelected As String, i As Integer 'Defining listbox1 of sheet TestDialog With Sheets["TestDialog"].ListBox1 'Using For loop for looping through all the items in List Box For i = 0 To .ListCount - 1 'Unselecting all the items in the list .Selected[i] = False Next i End With 'Deleting data from range K10 to K23 Range["K10:K23"].Select Selection.ClearContents Range["L10"].Select End Sub

If you liked this blog, share it with your friends onFacebook. Also, you can follow us onTwitterandFacebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at