How to use multiple listboxes on user form in Excel

Message from a website visitor:
‘Hello Sir,
I have watched your videos on youtube and visited your website; all the information is so helpful. My issue is that I am trying to make a userform in which i have 3 list boxes or textboxes. i want that when i select the value from first list box it shows the values associated with it in excel sheet in listbox 2 ( eg. in listbox 1 tv,computer,camera) when i select tv it shows me different brand names of tv which i have already written in one of the columns in excel sheet. ( eg. TV – Sony,LG,Panasonic etc) and after that when i select Sony under tv then it shows some information about Sony which is also written in excel sheet under some category name. So basically I have 3 steps 1. load data in LB1, step 2. Load data in listbox2 depend on listbox1. step 3. load data in listbox 3 depend on what i chose in listbox 2. thanks in advance for your help. ‘

Ravi

The process of creating the user-form with multiple list-boxes is easy but you need to be patient with the macro coding in VBA and think through the complete process.

First we should have our data in an Excel workbook
Now we create named ranges for the appropriate ranges
Based on this data our form will show the first category of data when the user-form is run or activated. We define a named range and access it via the ‘RowSource’ property of the list box
Next when an item is selected in listbox1 we display a list of values in the listbox2 using an ‘IF’ function. Indexes always start with 0. Therefore the first item is always indexed as 0 (zero).
Now based on the above two list boxes we can display values in the third listbox3 using the multiple ‘IF’ function
Finally we use a textbox to display a price of an item using VLOOKUP

The code for the various list boxes, textbox and command buttons is given below. The code does all the things that the website visitor has asked but you can refine it easily to give a better performance:
Private Sub CommandButton1_Click()
End
End Sub

Private Sub CommandButton2_Click()
TextBox1.Text = “”
End Sub

Private Sub ListBox1_Change()
If ListBox1.ListIndex = 0 Then
ListBox2.RowSource = “suppliers”
End If
If ListBox1.ListIndex = 1 Then
ListBox2.RowSource = “computers”
End If
If ListBox1.ListIndex = 2 Then
ListBox2.RowSource = “cameras”
End If
End Sub

Private Sub ListBox2_Click()
If ListBox1.ListIndex = 0 And ListBox2.ListIndex = 0 Then
ListBox3.RowSource = “sizes” ‘ named range
End If
If ListBox1.ListIndex = 0 And ListBox2.ListIndex = 1 Then
ListBox3.RowSource = “sizes”
End If
If ListBox1.ListIndex = 0 And ListBox2.ListIndex = 2 Then
ListBox3.RowSource = “sizes”
End If
If ListBox1.ListIndex = 1 And ListBox2.ListIndex = 0 Then
ListBox3.RowSource = “intel” ‘ named range
End If
If ListBox1.ListIndex = 2 And ListBox2.ListIndex = 0 Then
ListBox3.RowSource = “canon” ‘named range
End If
If ListBox1.ListIndex = 2 And ListBox2.ListIndex = 1 Then
ListBox3.RowSource = “notinstock” ‘named range
End If
If ListBox1.ListIndex = 2 And ListBox2.ListIndex = 2 Then
ListBox3.RowSource = “notinstock”
End If
End Sub

Private Sub ListBox3_Click()
If ListBox1.ListIndex = 0 And ListBox2.ListIndex = 0 And ListBox3.ListIndex = 0 Then
TextBox1.Value = Application.VLookup(Me.ListBox3, Sheets(“Details”).Range(“A:B”), 2, False)
End If
If ListBox1.ListIndex = 0 And ListBox2.ListIndex = 0 And ListBox3.ListIndex = 1 Then
TextBox1.Value = Application.VLookup(Me.ListBox3, Sheets(“Details”).Range(“A:B”), 2, False)
End If
If ListBox1.ListIndex = 0 And ListBox2.ListIndex = 0 And ListBox3.ListIndex = 2 Then
TextBox1.Value = Application.VLookup(Me.ListBox3, Sheets(“Details”).Range(“A:B”), 2, False)
End If
End Sub

Private Sub UserForm_Activate()
TextBox1.SetFocus
ListBox1.RowSource = “items”
End Sub
Watch the training video below to see how we implement the use of multiple list boxes on a user-form in MS Excel:


2 thoughts on “How to use multiple listboxes on user form in Excel

  1. zeak

    hi… sir,

    how to view the range of table (example, range “a2” to “h” rows.count) on list box from another workbook… thankyou…

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *