+91-8826547882

Create Multiple Selection Listbox in Excel VBA

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.

create Lisbox in excel

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

Click Here to download sample workbook template

Create Userform

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 userform will appear. Now drag lisbox from the Toolbox in the userform. Add one command button too.

Listbox in Microsoft Excel VBA

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”

Create multiple selection listbox in excel

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

Userform:
Name: Grocerylist
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 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
May 15, 2015

1 Responses on Create Multiple Selection Listbox in Excel VBA"

  1. Javier says:

    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

© copyright www.excelcrazy.com. All rights reserved. Design and Development by www.creativebrainweb.com