The VBA Guide For Using Userform ListBox Controls
What Are ListBox Controls?
ListBoxes are a great way to manage tables within your userform. I have used them in the past to allow users to manage rows of information without necessary displaying all that information within the userform itself. There are a lot of neat things you can do with a userform so I’ve collected some of the more popular tasks you may want to know how to write within your VBA code.
For all the example VBA code snippets, the name of the listbox with be called “ListBox1”. I also have a downloadable example file that you can get for free if you want to see a lot of this code in action.
Enjoy!
Add An Item to The ListBox
To The End
ListBox1.AddItem "Apple"To A Specific Position
Remember ListBoxes are zero based, so the first item is really at position 0. So if you want to add an item to the 5th position, you need to reference number 4 in the AddItem function.
'Add to the 2nd Position (subtract 1 from desired)
ListBox1.AddItem "Apple", 1Add Multiple Items To The ListBox
Individually Written
Sub ListBox_Load()
ListBox1.AddItem "Apple"
ListBox1.AddItem "Orange"
ListBox1.AddItem "Pear"
End SubFrom An Array List
Sub ListBox_LoadArray()
Dim myArray As Variant
myArray = Array("Apple", "Orange", "Pear")
ListBox1.List = myArray
End SubFrom A Cell Range
Sub ListBox_LoadRange()
Dim cell As Range
'Load to ListBox
For Each cell In Worksheets("Sheet1").Range("A1:A6")
ListBox1.AddItem cell.Value
Next cell
End SubFrom A Table Object (ListObject)
Sub ListBox_LoadTable()
Dim tbl As ListObject
Dim cell As Range
'Store Table Object to a variable
Set tbl = Sheet1.ListObjects("Table1")
'Load List Box
For Each cell In tbl.DataBodyRange.Columns(1).Cells
ListBox1.AddItem cell.Value
Next cell
End SubDelete ListBox Items
Remove An Item From The ListBox
'Remove 4th item in ListBox (subtract 1 from desired row)
ListBox1.RemoveItem 3Remove Selected Item(s) From The ListBox
Private Sub DeleteSelection()
'PURPOSE: Remove any selected items from the ListBox
Dim x As Long
Dim OriginalCount As Long
'Store original ListBox count
OriginalCount = ListBox1.ListCount
'Temporarily hide ListBox (runs faster)
ListBox1.Visible = False
'Delete selected line items
For x = OriginalCount - 1 To 0 Step -1
If ListBox1.Selected(x) = True Then ListBox1.RemoveItem x
Next x
'Unhide ListBox
ListBox1.Visible = True
End SubRemove All Items From The ListBox
ListBox1.ClearListbox Selected Items
Select A Specific Item In The ListBox
'Select the 5th item in the ListBox (subtract 1 from desired row)
ListBox1.Selected(4) = TrueDeselect All Items
Unfortunately, the “ListIndex = -1” method does not work when a ListBox allows for multiple selections. Hence, the below code tests for the ListBox’s selection mode.
If ListBox1.MultiSelect = fmMultiSelectSingle Then
ListBox1.ListIndex = -1
Else
For x = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(x) Then ListBox1.Selected(x) = False
Next x
End IfCount How Many Items Are Selected (Function)
Function ListBoxSelectionCount(LB As ListBox) As Long
'PURPOSE: Count how many items are selected in a give Listbox
Dim x As Long
Dim Count As Long
For x = 0 To LB.ListCount - 1
If LB.Selected(x) Then Count = Count + 1
Next x
ListBoxSelectionCount = Count
End FunctionCount How Many Items Are In The ListBox
'Return how many items are in the ListBox
MsgBox ListBox1.ListCountMove Selected Item Up/Down
Move Selection Up One Position
Sub MoveUp()
'PURPOSE: Move the selected item up one position in the list
Dim x As Long
Dim Count As Long
Dim Position As Long
'Is there an item selected?
If ListBox1.ListIndex = -1 Then Exit Sub
'Which Item is selected?
For x = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(x) = True Then
Position = x
Count = Count + 1
If Count > 1 Then Exit Sub 'More than 1 item selected
End If
Next x
'Selected item already at the top?
If Position = 0 Then Exit Sub
'Add an item above the current selection
ListBox1.AddItem ListBox1.List(Position), Position - 1
'Remove Original Selection
ListBox1.RemoveItem Position + 1
'Re-select the item that got moved
ListBox1.Selected(Position - 1) = True
End SubMove Selection Down One Position
Sub MoveDown()
'PURPOSE: Move the selected item down one position in the list
Dim x As Long
Dim Count As Long
Dim Position As Long
'Is A ValidSelection Made?
If ListBox1.ListIndex = -1 Then Exit Sub 'No Selection made
'Which Item is selected?
For x = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(x) = True Then
Position = x
Count = Count + 1
If Count > 1 Then Exit Sub 'More than 1 item selected
End If
Next x
'Move selected item down if not already at the bottom
If Position < ListBox1.ListCount - 1 Then
'Add an item below the current selection
ListBox1.AddItem ListBox1.List(Position), Position + 2
'Remove Original Selection
ListBox1.RemoveItem Position
'Re-select the item that got moved
ListBox1.Selected(Position + 1) = True
End If
End SubDownload The Excel Example File
If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.
Additional Resources
- Excel VBA UserForm Listbox (AnalysisTabs.com)
- The Complete Guide to Excel VBA Form Control ListBoxes (wellsr.com)
Anything To Add?
I know there are a TON of things you can do with ListBoxes and if there are actions you are stuck trying to figure out, leave a comment below and I will try to add them to the guide. Please only ask for generic tasks and not super-specific ones. Also, if there is a more simplistic way to carry out some of these tasks, let me know!
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.