You gonna get more information about Excel VBA Training in Gurgaon. In this article, learn how to create multiple selection list box in Excel VBA. We will be using User form, 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 list box. By clicking on the Add item button, we can add multiple items in the worksheet.
In this Article How to create multiple selection list box in Excel VBA. It Includes
- How to create user form
- How to create Listbox
- Adding Items to the list box
- Multiple Selection in the list box
- 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
Create User form
Open Visual Basic for Editor. Click Insert Tab, USerform.
Add Items in the Listbox
A blank user form will appear. Now drag list box from the Toolbox in the user form. Add one command button too.
Read on Excel VBA Training in Gurgaon…
Now select list box and add items to 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 user form from property window.
Name: Grocery list
Caption: Grocery List
Caption: Add Items
Now double-click on the Add Item command button and update below code.
Create multiple selection list box in Excel VBA
123456789101112131415161718192021222324252627282930313233343536 Private Sub additemscmdbttn_Click()Application.ScreenUpdating = FalseDim i As IntegerDim lastrow As LongRange("H6:I" & Rows.Count).ClearContentsRange("H5").Value = "Shopping List"Range("I5").Value = "Cost"i = 6With GrocerylistboxFor intItem = 0 To .ListCount - 1If .Selected(intItem) = True ThenCells(i, 8).Value = .Column(0, intItem)Cells(i, 9).Value = .Column(1, intItem)i = i + 1End IfNext intItemEnd Withlastrow = Range("I" & Rows.Count).End(xlUp).RowRange("I5").End(xlDown).Offset(1, 0).Formula = "=sum(I6:I" & lastrow & ")"Range("H5").End(xlDown).Offset(1, 0).Value = "Total"Application.ScreenUpdating = TrueEnd Sub
Ready to learn the next level? Want to feel like excellence in this?
Check out more in excel VBA training in Gurgaon today.