The VBA Guide For Using Userform ListBox Controls

The VBA Guide to interacting with 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!


 
Already Subscribed? Click   HERE   to log-in to the "Example Files" section

Already Subscribed? Click HERE to log-in to the "Example Files" section

 

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", 1


Add Multiple Items To The ListBox

Individually Written

Sub ListBox_Load()

ListBox1.AddItem "Apple"
ListBox1.AddItem "Orange"
ListBox1.AddItem "Pear"
  
End Sub

From An Array List

Sub ListBox_LoadArray()

Dim myArray As Variant

myArray = Array("Apple", "Orange", "Pear")

ListBox1.List = myArray

End Sub

From 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 Sub

From 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 Sub


Delete ListBox Items

Remove An Item From The ListBox

'Remove 4th item in ListBox (subtract 1 from desired row)
  ListBox1.RemoveItem 3

Remove 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 Sub

Remove All Items From The ListBox

ListBox1.Clear


Listbox Selected Items

Select A Specific Item In The ListBox

'Select the 5th item in the ListBox (subtract 1 from desired row)
  ListBox1.Selected(4) = True

Deselect 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 If

Count 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 Function


Count How Many Items Are In The ListBox

'Return how many items are in the ListBox
  MsgBox ListBox1.ListCount


Move 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 Sub

Move 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 Sub

Additional Resources

Anything To Add?

I know there are a TON of things you can do with ListBoxes and if there are actions your 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!