In this article, learn how to create multiple selection listbox in Excel VBA. We will be using Userform, ActiveX List box control and a command button in this.
User form will show a grocery list in the list box and we can select the multiple items from the listbox. By clicking on the Add item button, we can add multiple items in the worksheet.
In this Article How to create multiple selection listbox in Excel VBA. It Includes
- How to create userform
- How to create Listbox
- Adding Items in the listbox
- Multiple Selection in the listbox
- On click of command button, get the list in the worksheet and Total sum of amount
- How to write Sum function with variable in VBA
Open Visual Basic for Editor. Click Insert Tab, USerform.
Add Items in the Listbox
A blank userform will appear. Now drag lisbox from the Toolbox in the userform. Add one command button too.
Now select lisbox and add items in it from property window.
Go to Row Source and write the list range address (In our downloaded template we have data in List sheet and address is List!Range(“A2:B40”)) and Multiselect option as “1 – frmMultiSelectMulti”
Now change the name and caption of command button and userform from property window.
Caption: Grocery List
Caption: Add Items
Now double click on the Add Item command button and update below code.
Create multiple selection listbox in Excel VBA
Private Sub additemscmdbttn_Click() Application.ScreenUpdating = False Dim i As Integer Dim lastrow As Long Range("H6:I" & Rows.Count).ClearContents Range("H5").Value = "Shopping List" Range("I5").Value = "Cost" i = 6 With Grocerylistbox For intItem = 0 To .ListCount - 1 If .Selected(intItem) = True Then Cells(i, 8).Value = .Column(0, intItem) Cells(i, 9).Value = .Column(1, intItem) i = i + 1 End If Next intItem End With lastrow = Range("I" & Rows.Count).End(xlUp).Row Range("I5").End(xlDown).Offset(1, 0).Formula = "=sum(I6:I" & lastrow & ")" Range("H5").End(xlDown).Offset(1, 0).Value = "Total" Application.ScreenUpdating = True End Sub