Userform Listbox Conditional on Previous Selection

**gsurge** · 06-06-2011, 04:37 PM

Hello,

I have to populate a list box using the conditional value selected in a previous listbox. That value selected in the first list box corresponds to its own range of values listed in the excel sheet. What is the code that will populate the second list box with the range corresponding to the value of the first?

Attached is the sheet.

for example, When Cambridge is selected in the first Listbox, I would like another listbox to open with the range below "Cambridge MC" on the combolists tab.

Thanks

**royUK** · 06-07-2011, 02:43 AM

Try replacing your code with this & see if it is what you want

Private Sub cmdOK_Click()
    Dim ws As Worksheet
    Set ws = Worksheets("Output")
    ws.Cells(1, 1).Value = Me.listMNGR.Value
    Unload Me
End Sub

Private Sub listMNGR_Click()
    Dim lCol As Long
    lCol = Me.listMNGR.ListIndex + 4
    With ThisWorkbook.Sheets("combolists")
        output1.listMC.List = .Range(.Cells(2, lCol), .Cells(2, lCol).End(xlDown)).Value
    End With
    Me.Hide
    output1.Show
End Sub


Private Sub UserForm_Initialize()

    Dim cell As Range
    With ThisWorkbook.Sheets("combolists")
        listMNGR.List = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown)).Value
    End With
End Sub

**gsurge** · 06-07-2011, 09:01 AM

Thanks but the code does not populate the second list box (blank)

Its not exactly what i need to do. So first, I would like the trigger to remain on the cmdOK button, instead of the listMNGR_Click.

Also, I would like it to pull the corresponding list for the second list box depending on the first list box value chosen. I have updated the example sheet with more lists to choose from. I am assuming this will require some sort of indirect/dynamic syntax to reference the correct column, as it will change according the the selection in the first box. For the time being, I would like to retrieve the "MC" list for each name, not the "CG"

**gsurge** · 06-07-2011, 09:09 AM

in other words, is there a way to have the list box populate based on a search-type function of the excel sheet. So that the code will search for a header with the same name as what was selected in the first box, and retrieve the cells below it as the box fill??

**royUK** · 06-07-2011, 09:31 AM

It does populate the second listbox. Your explanation was confusing so I set it up to load what I took to be the required column.

Your manager selections do not match the other column headings

This does what I think you need. It will be much faster than a search

**gsurge** · 06-07-2011, 09:36 AM

is there a way to have this feed off a search for the header? I still cannot get it to populate correctly

**royUK** · 06-07-2011, 09:38 AM

See my previous post that I've edited

**gsurge** · 06-07-2011, 09:54 AM

thank you!

**royUK** · 06-07-2011, 10:08 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save