Bugs in Excel's ActiveX combo boxes?

Question

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 1

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.

Answer 2

For this reason, I use cells with data validation lists when putting combo boxes on a spreadsheet.

Answer 3

I'm having the same problem, I've seen similar strange resolution problems with combo boxes.

I've seen boxes get bigger and smaller on each subsequent click in them. SOOOO frustrating. I've got a few custom made sheets with linked cells, and they randomly change size.

I've seen the problem when the screen resolution changes (I've used them on a touch screen tablet)

These problems have essentially ruined a project i worked on. I'd LOVE it if someone could help

I've linked my comboboxes to cells, and have them filling based on a cell range within the sheet, say M10:$M15.

Answer 4

Me too. I am trying to use combo boxes with a ListFillRange from another sheet referenced by Name. When the Name range changes strange things happen with the combobox and sometimes Excel freezes or crashes. If the named range never changes, it is okay.

This is with Excel 2007 and I have installed all updates, to no avail.

I am going to abandon their use, but don't like to because of the 'immediate lookup' feature they provide.

al.f

Answer 5

Hi there, I am so glad to find some people with the same problem. I'm from Germany and did not find any answers on German homepages. Is there really NO solution or alternative? I'm currently using the old formular combobox instead of the Active X combobox, and everything is fine. I'm using names for dynamic ranges and it works. But - in contrast to Active X - the old formular comboboxes do not have much setting alternatives and I don't even know how much I can work in VBA with them, e.g. my comboboxes' contents are dependent on each other and I'd like to enable and disable them.

What a shame! Any ideas?

P.