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
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
End Sub
Advanced MultiSelect ListBox
MultiSelect ListBox
Download Workbook

Excel Tutorials : 100 Excel Tutorials

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

0 Response to "ListBox ActiveX Control Explained"

Post a Comment

Next → ← Prev