Excel Crazy

Create Multiple Selection Listbox in Excel VBA

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.

create Lisbox in excel

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

Click Here to download sample workbook template

Create User form

Open Visual Basic for Editor. Click Insert Tab, USerform.

Learn how to create multi selection listbox in Excel vba

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.

Listbox in Microsoft Excel VBA

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”

Create multiple selection listbox in excel
excel vba training in gurgaon

Now change the name and caption of command button and user form from property window.

User form:
Name: Grocery list
Caption: Grocery List

Command Button:
Name: addiemscmdbtn
Caption: Add Items

Now double-click on the Add Item command button and update below code.

Create multiple selection list box 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

Ready to learn the next level? Want to feel like excellence in this?
Check out more in excel VBA training in Gurgaon today.

1 responses on "Create Multiple Selection Listbox in Excel VBA"

  1. Excellent Post, very useful and easy to do it!! I am learning VBA in Excel estep by step. Thank you so much

    Regards from Venezuela

Leave a Message

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

top
 

© LOKESH PALIWAL 1987 - 2018

LOKESH@EXCELCRAZY[DOT]COM | 8826547882

Reporting Solutions | Data Consulting | Analytics Training

CONTACT US
close slider

Your Name (required)

Your Email (required)

Your Phone no. (required)

Your Message for us...

Skip to toolbar