Display different list in a list box when specific drop down chosen

**lwwc** · 12-17-2013, 12:06 PM

Hi, I'm new to the VBA and new to this forum.
Really hope to gain more knowledge and experience with everyone of you in this forum.

Currently I have project that I need to create a userform, within that userform, there are some drop downs and listbox.
Most of the part I think I can handle it expect one criteria which is let say if first drop down box (A, B, C), user choose "A" and the listbox below will show "A" List. If user choose "B", then listbox will change to "B" list, etc.

Is this possible in VBA?

**Leon V (AW)** · 12-17-2013, 12:22 PM

if A,B and C are named ranges it is easy just use.

Please Login or Register  to view this content.

if not you will have to do a match (to find the combobox text on the spreadsheet and use that location to defign the selected range

**lwwc** · 12-17-2013, 12:50 PM

Hi Leon V

Thanks for your help, and yes, I think in my case, I will need to do a match to pull the list from spreadsheet (sound like easier with this form). What will be the coding suggestion in this case?

Actually maybe I need to explain a bit more on what I need to do here

Supply Chain/Delivery -- this is one of the item in dropdown menu, once this select then below show on the listbox
Delivery Delays
Driver Experience
Accuracy of order - misfill
Accuracy of order - shortship/overship
Condition of packaging
Returns - delay in pick up
Returns - toner recycling

Pricing -- this is another item from the dropdown menu, if this choose, listbox will show below list instead of the above
Pre-login
Post-login
Contract discrepancy
Pricing discrepancy
Vendor pricing increase

Ideally initial userform when first open, the lisbox is empty.

Is that somewhat possible?

Thanks
Leo (lwwc)

**Leon V (AW)** · 12-17-2013, 01:15 PM

the following assumes that your bold text is column headers

Please Login or Register  to view this content.

and this version assumes they are row headers.

Please Login or Register  to view this content.

**lwwc** · 12-17-2013, 01:31 PM

Thanks again

Can you explain a bit on
Set found = Range("1:1").Find(name)
For Each selected In Range(Cells(2, found.Column).Address, found.End(xlDown).Address).Cells

As I'm so new that I try to understand this part.

I tried to place the code to my file but got Run-time error '91': Object variable or With block variable not set

Should I placed all my list at a certain worksheet / column within the file?

**Leon V (AW)** · 12-18-2013, 05:27 AM

{Set found = Range("1:1").Find(name)} searches row 1 for the combobox selection.
{For Each selected In Range(Cells(2, found.Column).Address, found.End(xlDown).Address).Cells} starts a loop to go through each cell bellow the found header
The code should be placed in the userform code module.

Edit: see attached for example.