Combo Box selection drives second combo box?

[Tcarey]

Hi,

I'm hoping this is a fairly simple issue, but I'm not sure about the most efficent way to go about performing the task.

I'm trying to create a combo box that contains a list of possible choices (Groups). Once a user selects the first group a sub-group would be avaliable for choice in the second box. Once the sub-group is selected another combo box would allow a choice of a sub-division group which after selection shows a specific code.

 

[Smitty]

See this post on Dependent Data Validation. It'll do exactly what you're looking to do.

HTH,

 

[Tcarey]

I'd like it to display in combo box lists just for the visual effect. I found the following code at http://blogs.captechconsulting.com/...eries-1-populating-cascading-combo-boxes-part

I'm not sure however to modify it to have more than one make for each category. Adding another value under the category and another make next to it will add the second option to the make box, but will also populate two of the same values in category box..

'This subroutine will spring into action when the user changes the
'Category combo box. It clears the Make combo box,
'updates the Advanced Filter criteria based on the user's category selection,
'filters the data to reflect makes linked to the selected category,
'and populates the Make combo box with the filtered data.
Private Sub cboCategory_Change()
    'Creates a string variable, Category, that stores the value of
    'the Category selected by the user.
    Dim Category As String
 
    'Sets the Category variable to the category value selected by the user
    Category = cboCategory.Value
 
    'Clears the filter criteria (row 2 in the Data worksheet).
    Worksheets("Data").Range("A2:C2").Clear
    'Sets the filter criteria based on the new selection.
    'Don 't worry if the code looks complicated to you, just focus on the key parts:
    'The range is where the formula will be written
    'i.e. in cell A2 on the Data worksheet)
    'and the Category variable
    Worksheets("Data").Range("A2").Formula = _
        "=" & Chr(34) & "=" & Chr(34) & "&" & Chr(34) & Category & Chr(34)
 
     'Clears the range where the results will be written
     Worksheets("Data").Range("G3:H26").Clear
 
    'Uses the Advanced Filter to create a unique set of make values that
    'are linked to the selected category.  Read the first part of the series,
    '"Populating Cascading Combo Boxes Part1" for details
    Worksheets("Data").Range("$A$3:$B$26").AdvancedFilter _
        Action:=xlFilterCopy, _
        criteriarange:=Worksheets("Data").Range("A1:B2"), _
        CopyToRange:=Worksheets("Data").Range("G3"), _
        Unique:=False
 
    'Clears the contents of the Make combo box, and sets the selected value
    'to nothing.  This prepares it to be populated by the newly filtered
    'set of values for make.
    cboMake.Clear
    cboMake.Value = ""
 
    'Uses a loop to step through each cell in the new make list
    'and adds each make to the combo box.
    'If a value is blank, it is not added.
    For Each cell In Worksheets("Data").Range("H4:H26")
        If IsEmpty(cell) = False Then
            cboMake.AddItem cell.Value
        End If
    Next cell
End Sub
'This subroutine will spring into action when the user changes the
'Make combo box.
'It updates the Advanced Filter criteria based on the user's
'category and make selections,
'filters the data to reflect models linked to the selected category and make,
'and populates the D column in the report with the resulting list of models.
Private Sub cboMake_Change()
    'Creates string variables, Category and Make, to store the values of
    'the Category and Make selected by the user.
    Dim Category As String
    Dim Make As String
 
    'Sets the Category variable
    'and Make variable to the values selected by the user
    Category = Worksheets("Report").cboCategory.Value
    Make = Worksheets("Report").cboMake.Value
 
    'Sets the filter criteria.
    'Read the post for details on this formula
    Worksheets("Data").Range("A2").Formula = _
        "=" & Chr(34) & "=" & Chr(34) & "&" & Chr(34) & Category & Chr(34)
 
    Worksheets("Data").Range("B2").Formula = _
        "=" & Chr(34) & "=" & Chr(34) & "&" & Chr(34) & Make & Chr(34)
 
    'Clears the previous list of models
    Worksheets("Data").Range("J3:L26").Clear
 
    'Uses the Advanced Filter to create a unique set of model values that
    'are linked to the selected Category.  Read the first part of the series,
    '"Populating Cascading Combo Boxes Part1" for details
    Worksheets("Data").Range("$A$3:$C$26").AdvancedFilter _
        Action:=xlFilterCopy, _
        criteriarange:=Worksheets("Data").Range("A1:C2"), _
        CopyToRange:=Worksheets("Data").Range("J3"), _
        Unique:=True
 
    'Copies the resulting list of models from the filtered list in the Data
    'worksheet to the Report worksheet (column D)
    Worksheets("Data").Range("L4:L26").Copy
    Sheets("Report").Select
    Range("D2").Select
    ActiveSheet.Paste
    Range("D2").Select
End Sub

 

[Simmerin]

I too am trying to do something similar to this. I have a data validation that indirectly based on the named range selected will spit out a list of contacts that work at that company. I am trying to go to a form type setup; when the workbook opens a form pops up where you select the company(which is the named range that the contacts come from). How can i make it so that in the form you select a customer, and that customer provides a vlookup and spits out a contact combobox on the same form?

i.e. (example) you click customer drop down select Microsoft and then the combobox has bill gates and other microsoft employess only

 

[Tcarey]

See this post on Dependent Data Validation. It'll do exactly what you're looking to do.

HTH,

Thanks, I was able to make a version like decsribed in the thread. However, the benefit to creating a combo box is not only the added look, but also the fact that the data is cleared in the box after each change in selection. The dependent data validation step requires manual deletion of the cell.

 

[Smitty]

You can use a Change event to take care of that.

But if you're interested in the ComboBox method, you'll find a tutorial here. Although it doesn't address the dependency issue, but that can be taken care of in code with the Find method and AddItem.