ComboBox Linage

[DonHuff]

How do I link the range of a comboboox to the results from another combobox. For example, I have a form I use to record time intervals over the day. If I select 9:00 for the first combobox, I would like the next combobox range to start at 9:00 instead of the default range 6:00. as I have 35 time intervals, I would like to place a formula in the combobox formatting screen rather than create 35 individual ranges.

[Loz]

Try the code below in the Change event of the first combo box.

Please Login or Register  to view this content.

This copies the fill range used by the 1st combo box to the 2nd one, but starting from the value selected in the 1st combo box. Any value already in the 2nd box is retained if it is still valid, otherwise it is deleted.

If no value is selected in the 1st box, the 2nd box references the entire fill range of the 1st box.

[DonHuff]

So, would I need to create 35 of these?

Also, s there some way to do this in the Forms menu of Excel rather than through MACRO?

[Loz]

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....

Please Login or Register  to view this content.

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.