This tutorial explains how to use ListBox ActiveX Control.
Simple ListBox (Without VBA)
Step I : Go to Developer Tab
Click on "Categorized" and then select "1 - fmMultiSelectMulti" from the drop down in "MultiSelect" box.
Step V : Save and Reopen the Workbook once to load the items in listbox
Filter Data using ListBox
Click on Filter Button and enter the following code
Download Workbook
Simple ListBox (Without VBA)
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()Step IV : Right click on ListBox and Select Properties >>
'Clear ListBox Items
With Sheet1.ListBox1.Clear
End With
End Sub
Click on "Categorized" and then select "1 - fmMultiSelectMulti" from the drop down in "MultiSelect" box.
ListBox MultiSelect Options |
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()Click on Clear Button and enter the following code
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
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 |
Share Share Tweet