list box with source range

How to get selected values from Excel list box form

In this article we will learn how to create multiselect list box on the Excel worksheet manually and how to set selected values into cells using VBA.

First off all let’s create list box on the sheet:

  1. Go to Developer tabInsert,
    selecting list box from form controls
  2. Select List Box from Form Controls,
  3. Place it anywhere you want on the sheet,
  4. Right click on list box and from context menu select Format Control…,
  5. Navigate to Control tab and select input range which will be the source data for our list box. Let’s assume it is G2:G10,
  6. Choose Multi as Selection type in the same tab and click OK

As a result after filling the range mentioned in point 5 I got the following:
list box with source range
Now we need to write little VBA code:

  1. Right click on list box and select Assign Macro…,
  2. In the opened Assign Macro window you will see macro name which will be assigned to list box (it means that after every selection in list box mentioned macro will be triggered), then click New (VBA Editor will be opened)
  3. Paste the following code (in my case macro name was ListBox1_Change):

  4. Switch to worksheet and test.

Working version of macro-enabled workbook with multiselect list box is here.

Leave a Reply

Your email address will not be published. Required fields are marked *