Bugs in Excel's ActiveX combo boxes?
I have noticed that I get all sorts of annoying errors when:
- I have ActiveX comboboxes on a worksheet (not an excel form)
- The comboboxes have event code linked to them (eg, onchange events)
- I use their listfillrange or linkedcell properties (clearing these properties seems to alleviate a lot of problems)
- (Not sure if this is connected) but there is data validation on the targeted linkedcell.
I program a fairly complex excel application that does a ton of event handling and uses a lot of controls. Over the months, I have been trying to deal with a variety of bugs dealing with those combo boxes. I can't recall all the details of each instance now, but these bugs tend to involve pointing the listfillrange and linkedcell properties at named ranges, and often have to do with the combo box events triggering at inappropriate times (such as when application.enableevents = false). These problems seemed to grow bigger in Excel 2007, so that I had to give up on these combo boxes entirely (I now use combo boxes contained in user forms, rather than directly on the sheets).
Has anyone else seen similar problems? If so, was there a graceful solution? I have looked around with Google and so far haven't spotted anyone with similar issues.
Some of the symptoms I end up seeing are:
- Excel crashing when I start up (involves combobox_onchange, listfillrange->named range on another different sheet, and workbook_open interactions). (note, I also had some data validation on the the linked cells in case a user edited them directly.)
- Excel rendering bugs (usually when the combo box changes, some cells from another sheet get randomly drawn over the top of the current sheet) Sometimes it involves the screen flashing entirely to another sheet for a moment.
- Excel losing its mind (or rather, the call stack) (related to the first bullet point). Sometimes when a function modifies a property of the comboboxes, the combobox onchange event fires, but it never returns control to the function that caused the change in the first place. The combobox_onchange events are triggered even when application.enableevents = false.
- Events firing when they shouldn't (I posted another question on stack overflow related to this).
At this point, I am fairly convinced that ActiveX comboboxes are evil incarnate and not worth the trouble. I have switched to including these comboboxes inside a userform module instead. I would rather inconvenience users with popup forms than random visual artifacts and crashing (with data loss).
Answer
I don't have a definitive answer for you, but I can tell you that I stopped using ListFillRange and LinkedCell for ActiveX controls about 10 years ago. I don't recall what particular problems I encountered. I just remember coming to the conclusion that whatever little time they saved me isn't worth the brain ache of trying to track down the bugs. So now I populate the controls through code and deal with output in the events.