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:
- Go to Developer tab→Insert,
- Select List Box from Form Controls,
- Place it anywhere you want on the sheet,
- Right click on list box and from context menu select Format Control…,
- 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,
- Choose Multi as Selection type in the same tab and click OK
- Right click on list box and select Assign Macro…,
- 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)
- Paste the following code (in my case macro name was ListBox1_Change):
Visual Basic1234567891011Sub ListBox1_Change()Dim i As IntegerRange("H2", Columns("H").SpecialCells(xlCellTypeLastCell)).Delete ' Remove column values where selected values are putWith ActiveSheet.Shapes("List Box 1").OLEFormat.Object ' List Box 1 is the name of list box, see Name Box on the image aboveFor i = 1 To .ListCountIf .Selected(i) = True ThenRange("H" & 1 + i).Value2 = .List(i)End IfNext iEnd WithEnd Sub
- Switch to worksheet and test.
Working version of macro-enabled workbook with multiselect list box is here.