Cascading Combo Box Help

Hello-
I am a new user and struggling with creating cascading combo boxes or dropdown boxes. (I would rather have combo boxes because the drop down arrow is always visible) I have gotten the dropdown to work somewhat, but I fear that dropdown boxes do not work with numerical values? Is there any way around this?

With that said, I am completely lost using active X combo boxes. Can anyone take a look at this and help?

Thanks-

Mike

Hello there,

Select the CUI worksheet, then right click on the worksheet's tab. In the blank space provided, next to the cursor copy and paste the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Me.ComboBox1   'with the combobox
        'if the selected cell in not in the range D18 through G18 then
        If Not Intersect(Range("D18:G18"), ActiveCell) Is Nothing Then
            .Top = ActiveCell.Top   'set distance from the top of the combobox equal to the active cell top
            .Left = ActiveCell.Left 'set the left the same distance as the active cell
            .Width = ActiveCell.Width   'set width equal to the active cell's width
            .LinkedCell = ActiveCell.Address    'set the linked cell equal to the activecell's address
            .Visible = True 'set the combobox to be visible
                Select Case Target.Column   'if the selected cell's column is in
                    Case 4  'column 4 (D)
                        .ListFillRange = "Lists!C5:C6"  'set the list fill range to the appropriate column on the CUI worksheet
                    Case 5  'column 5 (E)
                        .ListFillRange = "Lists!D5:D7"  'set the list fill range to the appropriate column on the CUI worksheet
                    Case 6  'column 6 (F)
                        .ListFillRange = "Lists!F4:F25" 'set the list fill range to the appropriate column on the CUI worksheet
                    Case 7  'column 7 (G)
                        .ListFillRange = "Lists!H5:H9"  'set the list fill range to the appropriate column on the CUI worksheet
                End Select  'end the select case
        Else    'if it's not in the range then
            .Visible = False    'hide the combobox
            .LinkedCell = ""    'set it's linked cell to nothing
        End If
    End With
End Sub

Close out of Visual Basic. Make sure the Design mode button is not clicked under the developer tab and then click on cells E18

Thank you so much for writing this code for me. It is very generous. I am still having difficulties however, I'm sure the problem is in the interface between the keyboard and the seat.

I have attached an updated spreadsheet. I am trying to get the first selection of Units to drive the slections of diameter and length. For example, if metric is selected, I want the selections of diameter and length to consist of only metric options.

How do I do this?

Once again, Thank you.

Mike

Hello there,

It's okay it gets confusing. The code is based on one activex combobox being on the worksheet with the name combobox1. When you click cell D18, E18, F18, and G18 the combobox shows and the row source is automatically updated to the range of cells in the Lists worksheet that correspond with the selected column. Attached is your original worksheet corrected so that it will work properly. To see the worksheet work, make sure to enable macro and then select a cell in the range D18:G18.

Thanks again....

When I select inch in the units dropdown, it should give me a list of diameters in inches...such as 9'-6", 11'-6" and 13'6" for example. The diameters and lengths are always in meters. How do I correct this.

Sorry if I'm making this more difficult than it needs to be.

Mike

Change the following line of code:

Case 4  'column 4 (D)
                        .ListFillRange = "Lists!C5:C6"  'set the list fill range to the appropriate column on the CUI worksheet

To:

Case 4  'column 4 (D)
                        .ListFillRange = "Lists!E5:E8"  'set the list fill range to the appropriate column on the CUI worksheet

Please take the time to read the code, anything that appears in green is a comment meant to help you understand what each line of code is doing. These comments should help you adjust the code to meet your needs. To view the code, Press Alt+F11.

Thanks!

*If this solves your post please mark this thread solved and maybe give a little star tap if I was able to help.