How to use multiple listboxes on a user-form in MS-Excel

Home More Videos











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 listboxes or textboxes. i want that when i select the value from first listbox 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 colums 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 writen 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.
  1. First we should have our data in an Excel workbook
  2. Now we create named ranges for the appropriate ranges
  3. 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' propw\erty of the listbox
  4. Next when an item is selected in listbox1 we dispaly 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).
  5. Now based on the above two listboxes we can dispaly values in the third listbox3 using the multiple 'IF' function
  6. Finally we use a textbox to display a price of an item using VLOOKUP
The code for the various listboxes, 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 listboxes on a user-form in MS Excel:





Watch the video on youtube

Further Links
How to use a listbox on a user-form
IF ELSE Function
How to use named ranges in Excel
Command Buttons on User Forms
Learn Macros step by step