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.

Let’s understand with the simple exercise:-

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

Data

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

ArrowInsertingListBox

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

ArrowPropertiesDialogBox

ArrowPropertiesDialogBox-2

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

ArrowCommandButton

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.

ArrowAssigningMacro

AssigningMacro

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 on Facebook. Also, you can follow us on Twitter and Facebook.

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 info@exceltip.com

Hans
May 20, 2019 at 9:29 am

Hi,
This is almost what I'm looking for..
I have this list with different descriptions, let's say one, two, three etc up to about 25.
I want to show all items in a form, let the user select (with checkbox) the items that are wanted, then click a cmdbutton which puts the checked items in a range on a worksheet. (Only one item per row) Is that possible??

Reply ↓