What is the best approach to produce two drop down menus, where the contents of the second are depedent on the value chosen from the first?
Would the second drop down just be created blank and each time I select a new value from the first drop down I "overwrite" some sort of attribute referring to the second drop down? Or should I re-create the second drop-down menu every time the first dropdown is clicked?
Any code snippets are most welcome!
It depends on what sort of dropdown you are using. If you are using data validation to have an in-cell dropdown then you can set the source of the second dropdown cell to an indirect function then you have your data in named ranges which reference the first dropdown. More info here: http://www.contextures.com/xlDataVal02.html
This can get tedious creating all the named ranges if you have a large amount of data in the first dropdown.
I have also done this in a userform with VBA.
Public Branches() As String Public Function List_Branch_Set(lngRegion As String) As Long Dim lngAllBranches As Long Dim lngReg As String Dim lngBranches As Long Dim lngIdx As Long Dim rw As Long lngAllBranches = Sheets("sheet1").Range("C1").Value lngBranches = CountBranches(lngRegion) If lngBranches > 0 Then ReDim Branches(lngBranches - 1) For rw = 2 To lngAllBranches + 1 lngReg = Sheets("sheet1").Cells(rw, 2).Value If lngReg = lngRegion Then Branches(lngIdx) = Sheets("sheet1").Cells(rw, 1).Value lngIdx = lngIdx + 1 End If Next rw End If List_Branch_Set = lngBranches End Function My data is then stored on sheet1 in the format: Branch Region
The function CountBranches() counts the number of rows in this list which match the selected region. Then you need a trigger on the first cbo.
Private Sub cboRegion_Change() Dim lngNum As Long Me.cboBranch.Clear lngNum = List_Branch_Set(NullHandleText(Me.cboRegion)) If lngNum <> 0 Then Me.cboBranch.List = Branches End If End Sub 