How To Make Cascading ListBoxes?

S

sharky

New Member
Joined
Jul 21, 2008
Messages
10
Hi,

I am still quite new to VBA and i am trying to write a program in excel where in a userform there are 2 listboxes, in the first you choose the product you want to enter and the second only populates once you have chosen a product and it populates with the possible prices you can use which are in the columns next to it on the settings page.

Any help would be very much appreciated,

Sharky:confused:
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Assuming you have a list of Products in cells A1 through A5 and a list of prices for each product separated by commas in cells B1 through B5 the code below should work for you.

Column A
Product A
Product B
Product C
Product D
Product E

Column B
$1.00, $1.25, $1.50, $1.75
$2.00, $2.25, $2.50, $2.75
$3.00, $3.25, $3.50, $3.75
$4.00, $4.25, $4.50, $4.75
$5.00, $5.25, $5.50, $5.75

Code: 
Option Explicit
Private Sub ListBox1_Click()
    ListBox2.Clear
    ListBox2.Visible = True
    Select Case ListBox1.ListIndex
        Case 0
            ListBox2.AddItem FormatCurrency(Split(Range("B1").Value, ",")(0), 2)
            ListBox2.AddItem FormatCurrency(Split(Range("B1").Value, ",")(1), 2)
            ListBox2.AddItem FormatCurrency(Split(Range("B1").Value, ",")(2), 2)
            ListBox2.AddItem FormatCurrency(Split(Range("B1").Value, ",")(3), 2)
        Case 1
            ListBox2.AddItem FormatCurrency(Split(Range("B2").Value, ",")(0), 2)
            ListBox2.AddItem FormatCurrency(Split(Range("B2").Value, ",")(1), 2)
            ListBox2.AddItem FormatCurrency(Split(Range("B2").Value, ",")(2), 2)
            ListBox2.AddItem FormatCurrency(Split(Range("B2").Value, ",")(3), 2)
        Case 2
            ListBox2.AddItem FormatCurrency(Split(Range("B3").Value, ",")(0), 2)
            ListBox2.AddItem FormatCurrency(Split(Range("B3").Value, ",")(1), 2)
            ListBox2.AddItem FormatCurrency(Split(Range("B3").Value, ",")(2), 2)
            ListBox2.AddItem FormatCurrency(Split(Range("B3").Value, ",")(3), 2)
        Case 3
            ListBox2.AddItem FormatCurrency(Split(Range("B4").Value, ",")(0), 2)
            ListBox2.AddItem FormatCurrency(Split(Range("B4").Value, ",")(1), 2)
            ListBox2.AddItem FormatCurrency(Split(Range("B4").Value, ",")(2), 2)
            ListBox2.AddItem FormatCurrency(Split(Range("B4").Value, ",")(3), 2)
        Case 4
            ListBox2.AddItem FormatCurrency(Split(Range("B5").Value, ",")(0), 2)
            ListBox2.AddItem FormatCurrency(Split(Range("B5").Value, ",")(1), 2)
            ListBox2.AddItem FormatCurrency(Split(Range("B5").Value, ",")(2), 2)
            ListBox2.AddItem FormatCurrency(Split(Range("B5").Value, ",")(3), 2)
    End Select
End Sub
Private Sub UserForm_Initialize()
Dim i As Integer
    For i = 1 To 5
        ListBox1.AddItem Range("A" & i).Value
    Next i
    
    ListBox2.Visible = False
    
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
populating a textbox on a userform from selections that are made from a listbox that is one another userform (popup)...
Replies
4
Views
265
kbishop94
K
N
  • Question
Double Click Macro that creates new sheet using SQL query
Replies
1
Views
165
Fluff
Fluff
J
  • Solved
Filter a listbox containing 12 columns using a single searchbox
Replies
8
Views
185
Anthony47
A
C
  • Question
Excel Userform Listbox Search issues
Replies
3
Views
273
Akuini
Akuini
P
  • Question
Userform Listbox Edit Rows Within Sheet
Replies
1
Views
499
pure vito
P

Forum statistics

Threads
1,216,075
Messages
6,128,665
Members
449,462
Latest member
Chislobog

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top