Nested Controll Combo Box issues

[nzjohnny]

Hi

I am having a issue with my nested combo boxes on Excel 2003. The ListfillRange of the 2nd combo box is a named range populated using the Offset formula from the value selected in the first combo box. All settings are in the Properties except for a VBA line which resets the default text for the 2nd combo box after an item is selected in the 1st combo box (using ListIndex = 0).

The Issue: The drop down list does not return all values in the named offset range. The number of rows seems to be restricted by the rowcount of the combo box 1 selection from when the spreadsheet is first opened. If I save and open the sheet with the combobox_1 item with the most number of rows (in this scenario the full rowcount appears under combobox_2), and subsequently select an item with less linked rows, the combobox_2 drop down shows blanks and duplicates (not in the source data) - pic is attached. Is there a way to reset the Combobox_2 dropdown settings at the time of selecting an item in Combobox_1?

Thanks is advance.

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[nzjohnny]

Example attached:

To clarify the issue: The Combo box is not displaying all the rows available in the ListFillRange.

Background: The spreadsheet uses 2 nested combo boxes. The first combo displays a list of types of change, then the second displays the list of subtypes restricted by the selection in combo box 1.

Steps to recreate the Issue in the attached workbook:

1 – Open workbook and leave Combo box 1 at the previously saved value.
2 – Open Combo box 2 and note the number of rows available to select from
3 – Select a new value in Combo box 1
4 – Re-open Combo box 2. You will notice that the number of lines displayed in the combo box 2 drop down are the same as in Step 2. If the source data actually has less rows than at step 2, the displayed list shows blank lines and if you scroll up and down, the list gets completely scrambled (duplicates appear etc). If the source data has more rows than at Step 2, then the additional lines do not appear and cannot be selected. Saving the Workbook with the changed Combo box 1 value (step 4) and then re-opening the workbook - the opposite will occur ie the correct number of rows will display.

It appears that the List of values displayed row count for Combo box 2 is not reset after changing Combo box 1.

Question: Is there a combo box attribute that can be reset each time the drop down arrow is clicked to ensure the displayed number of rows is correct?
Thanks