Excel: combobox listfillrange property pointing at a formula-based named range; combobox with a linkedCell, equals bugs

Question

ActiveX combobox objects in Excel do not behave well when their ListFillRange refers to a formula-based Named Range (Defined Name).

I think I have encountered other errors and possibly even Excel crashes thanks to this, but right now all that happens is the combobox_change() event is triggered anytime ANY cell in the workbook is changed.

I am not sure if this is really a bug, or if there is a fix, or a workaround. If it is a bug, how do I report it to the Excel people?

And finally, the real meat of my question is "How do I work around this issue best?" I would like to have some formula-based named ranges, but it seems like this won't be possible.

To reproduce this bug, do the following:

1. Create a new workbook. On Sheet3, create a small table 3 columns across, and several rows high.
2. Create a named range with this formula (or an equivalent): =OFFSET(Sheet3!$A$2:$C$36,0,0,COUNTA(Sheet3!$A:$A),COUNTA(Sheet3!$4:$4)) To do this use Input>Name>Define. Name the range something like "demoRange"
3. Go to Sheet1 and create a combobox, (it must be on a separate sheet). (Use the Control Toolbox menu, not the Forms menu).
4. Click on the Design Mode button (the blue triangle with pencil), then right click on the combo box and go to Properties.
5. In the properties window for the combobox, change the ListFillRange property so that it points at the named range you created in step 2 ("demoRange").
6. You may want to change the ColumnCount property to 3, and the ColumnWidths property to "50,50,50"
7. Set the linkedCell property to cell "A1" by typing A1 in the linkedCell property.
8. Close the properties window, and double click on the combobox to define its change() event.
9. Put a Debug.Assert(false) or Msgbox("demo") line in the subroutine for the new combobox's change event.
10. Exit design mode
11. important - Now select an item in the combobox. The event should trigger normally the first time. (The bug will not show if you don't do this step--something must be selected in the combobox)
12. Edit cells anywhere in the workbook [Edit] or any other open workbook [/edit], on any sheet and any location. Each time you edit any cell, (at least for me), the onchange event for the combo box is run.

Again, is this normal, and what is the best alternative for what I am doing? This combo box gets linked to various cells, and is supposed to be a replacement for the tiny font in the data validation dropdowns excel provides by default.

Answer 1

I have a few options available that I am aware of thus far. The best I can come up with is this:

Avoid directly using formula-based named ranges. Instead, define a subroutine that will check whether the defined range "demoRange" should be changed from what its current value is. Run this subroutine on the workbook_open and sheet3_deactivate events. If needed, prompt the user to ask if it's all right to update the named range. [edit] The macro that updates "demoRange" could probably just copy from a "demoRange_FormulaBased" named range into "demoRange" which would be static. [/edit]

This solution works well because you can keep using the linkedcell property, you don't have to use VBA to populate the comboboxes, and the named range can still be used for whatever other purposes it already had. Avoid using the onchange event to run this new subroutine, since it might end up being triggered thousands of times if a user opens the Find/Replace dialog and chooses "Replace All".