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.