ListBox ActiveX Control Explained

Best Online Course : SAS Programming with 50+ Case Studies

- Explain Programming Concepts in Simple English
- Live Projects & Case Studies
- Job Placement Assistance
- Get 10% off till Oct 26, 2017
- Batch starts from October 28, 2017

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

Excel Tutorials : 100 Excel Tutorials

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has close to 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like retail and commercial banking, Telecom, HR and Automotive.


While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

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