ComboBox Linage

Apologies, I had assumed that your comboboxes had been set-up through the Control Toolbox menu items.

Using the Forms menu, comboboxes and the like become Excel Shapes, and so need a different approach when programming them.

First, you will need to know the names of the two comboboxes. This is simply done by left-clicking on each in turn whilst holding down the [Ctrl] key. Their names will appear in the names area in the top left of the screen (where you usually see the cell references or names). Note these names down (being careful to note if there are any spaces).

Then, on the first combobox, left-click whilst holding down the [Ctrl] key and select the Edit Code item from the Forms menu. This should bring you into the Visual Basic windows and you should be presented with a couple of lines somethng like the following -

Sub DropDown1_Change()

End Sub

Between these two lines you can copy & paste the following code....

On Error Resume Next
Dim rng As Range

Const ComboOne$ = "Drop Down 1"
Const ComboTwo$ = "Drop Down 2"

With ActiveSheet.Shapes(ComboOne$).OLEFormat.Object
Set rng = Range(.ListFillRange)
ActiveSheet.Shapes(ComboTwo$).OLEFormat.Object.ListFillRange = _
    Range(rng.Offset(.ListIndex - 1, 0), rng.Cells(rng.Rows.Count, rng.Columns.Count)).Address
End With
ActiveSheet.Shapes(ComboTwo$).OLEFormat.Object.ListIndex = 0

The two names highlighted in red above need to be changed to whatever the names of your comboboxes are.

Whenever the 1st combobox is changed, the available items in the 2nd box will start from the value of the 1st box. Any value in the 2nd box will be deleted.

This short program only has to be set-up once against the 1st combobox (no code is needed for the 2nd combobox). The code assumes that the 1st combobox has already been set-up with the input range for the lookup values.