ListBox ActiveX Control Explained

Deepanshu Bhalla Add Comment ,
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
Related Posts
Spread the Word!
Share
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.

Post Comment 0 Response to "ListBox ActiveX Control Explained"
Next → ← Prev