dropdowns

J

jmorgs

I have two lists which are connected to eachother. For instance, whe
human factors is chosen, the user then needs to choose from safety an
comfort. Then, depending on whether they choose safety or comfort
there is another list they need to choose from. However, no
everything on the first list has items on the second list, some of th
things on the first list only have things on the third list.
Because the use is choosing these values I now have it set up a
dropdowns in the cells. Now I have a formula as the source in th
dropdown that uses OFFSET and Match functions, saying whatever i
chosen in the previous cell, count how many rows down that is on thi
other list, and go that many columns over. This way, whatever is chose
in the first list impacts what option the user has to choose from in th
second list.
The problem I have having is when the first list has no values in th
second list. I would like it to not have a dropdown list for te
second value (because there is nothing to choose from) I know this is
compicated question but I am thinking maybe a loop but I'm not sure
 
J

jpendegraft

Below is some code that addressed a similar situation...based on th
state selected, the dropdown populated with the appropriat
wholesalers....

Note..this is used on a userform with comboboxes....

Sub FilterWholesalers()
Dim Row As Integer
Set testWholesaler = ThisWorkbook.Sheets("lookup on brewery no
refrig")
Set TargetCell = Cell.Value

TopicCount
Application.WorksheetFunction.CountA(testWholesaler.Range("A:A"))

For Row = 1 To TopicCount
If TargetCell(Row, 4) = cmState.Value Then
cbWholesaler.AddItem StateList.Cells(Row, 1)
End If
Next Row
cbWholesaler.ListIndex = 0
CurrentTopic = 1
End Su
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top