ListBox ActiveX Control Explained

This tutorial explains how to use ListBox ActiveX Control.

Simple ListBox (Without VBA)

Step I : Go to Developer Tab

Step II : Click on Insert Button and then select ListBox under ActiveX Controls (see the image below)

ListBox ActiveX Control

Step III : Right click on ListBox and Select Properties >>

Click on "Categorized" and then provide input range in "ListFillRange" box and give value to "LinkedCell" box where you want selected value to be placed.

List Box Properties

Step IV : Click on Design Mode

ActiveX Control : Click on Design Mode

MultiSelect ListBox (with VBA)

Step I : Insert ListBox (ActiveX Controls)

Step II : Type Input Range in cells N5:N11

Input Range

Step III : Press ALT F11 to open VB Editor and then go to "ThisWorkbook" and paste the following code

Private Sub Workbook_Open()
'Fill List Box Items
i = 5
Do Until Sheet1.Cells(i, 14).Value = ""
x = Sheet1.Cells(i, 14).Value
Sheet1.ListBox1.AddItem (x)
i = i + 1
Loop
End Sub

Sub SelectMultipleItems_ListBix()
'To Select Multiple Items in ListBox
With Sheet1.ListBox1.MultiSelect = fmMultiSelectMulti
End With
End Sub

Sub Clear_ListBox()
'Clear ListBox Items
With Sheet1.ListBox1.Clear
End With
End Sub

Step IV : Right click on ListBox and Select Properties >>
Click on "Categorized" and then select "1 - fmMultiSelectMulti" from the drop down in "MultiSelect" box.

ListBox MultiSelect Options

Step V : Save and Reopen the Workbook once to load the items in listbox

Filter Data using ListBox

Assuming data that you want to filter are entered in B14:B44 in sheet1.

Step VI : Insert 2 Command Buttons and name them "Filter" and "Clear" (Go to Developer Tab >> Click on Insert >> Click on Command Button under ActiveX Controls)

Filter Data using ListBox

Click on Filter Button and enter the following code

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim x As Variant
ReDim x(0)
For i = 0 To ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
x(UBound(x)) = Me.ListBox1.List(i)
ReDim Preserve x(UBound(x) + 1)
End If
Next i
Range("B14:B44").AutoFilter Field:=1, Criteria1:=x, Operator:=xlFilterValues
Application.ScreenUpdating = True
End Sub

Click on Clear Button and enter the following code

Private Sub CommandButton2_Click()
Dim i As Integer
On Error Resume Next
For i = 0 To ListBox1.ListCount - 1
Me.ListBox1.Selected(i) = False
Next i
ActiveSheet.ShowAllData
End Sub

Advanced MultiSelect ListBox

MultiSelect ListBox

Download Workbook

About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

While I love having friends who agree, I only learn from those who don't
Let's Get Connected Email LinkedIn