Excel With VBA

How do you use ListBox in Excel?

What is ListBox control?

The list box allows you to see multiple items in one view and
allows the user to select one or multiple items in the list.

It helps you to manage a big worksheet, and allows you to put constraints on
your data lists.

You can design it with the ability to select one item, or multiple item.
IMO, if you are only going to allow selecting 1 item, why not just use a ComboBox?

IMO, a ListBox’s purpose is to allow selecting multiple items

Here is a ListBox with the multiselect option selected:

How do I use listbox in VBA?

Here I am using a ListBox to display the values in column A of my worksheet:

I am loading the UserForm with a command button

The button is called “btnOpenForm”, and I am programming the click event:

Private Sub btnOpenForm_Click()
    UserForm1.Show
End Sub

BTW, here is the code for adding the items from your sheet to your ListBox:

Private Sub UserForm_Initialize()
    LoadBoxes
End Sub


Sub LoadBoxes()
    Dim intCounter As Integer
        
    'load listbox
    With Me.lstSuppliers
        .Clear
        For intCounter = 1 To 132
            .AddItem Sheets("Suppliers").Cells(intCounter, 1).Value
        Next intCounter
    End With
End Sub

{if you can’t interact with your new button, like click on it, make sure you’re out of “Design Mode”)

Make the setting on your listbox in the “MultiSelect” property: frmMultiSelectExtended (frmMultiSelectMulti allows you to select without the Shift or Ctrl keys, IMO, it’s not normal for users so don’t use it)

To select multiple items from your new ListBox, hold down the “CTRL” key while you left click. With consecutive items, hold down the “Shift” key.

Then show the items you just selected by clicking on “Display Selected Items”:

Here is the code to loop through the selected items:

Private Sub btnSelected_Click()
    Dim intItem As Integer
    Dim strItems As String
    
    For intItem = 0 To Me.lstSuppliers.ListCount - 1
        
        'if the item is selected then we are going to show it.
        If Me.lstSuppliers.Selected(intItem) Then
            
            'display the selected items
            MsgBox Me.lstSuppliers.Column(0, intItem)
            
        End If
    Next intItem
End Sub

Now you can use the selected ListBox items, and write them to other cells, or other places on your worksheet.

Let me know if you have any questions.

admin