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

My active-x combo box works fine when my Dell is docked but resizes to a larger font each time it is clicked when the Dell is undocked - very strange. I added resizing code which works when undocked, but both .height and .scaleheight fail when docked and when triggered programmatically (even stranger).

        Sheet2.Shapes("cb_SelectSKU").Select
        Selection.ShapeRange.Height = 40
        Selection.ShapeRange.ScaleHeight 0.8, msoFalse, msoScaleFromTopLeft

I then added my own enableevents-like switch so that the resizing only occurs when a user selects a combobox value, not when anything is affected while a macro is running.

Select Case strHoldEvents
    Case Is = "N"                                                   'Combobox resizing fails with error when triggered programatically (from SaveData)

        Call ShowLoadShts

        Sheet2.Shapes("cb_SelectSKU").Select
        Selection.ShapeRange.Height = 40
        Selection.ShapeRange.ScaleHeight 0.8, msoFalse, msoScaleFromTopLeft


    Case Else
End Select

Finally that seems to work, whether docked or undocked, whether triggered by the user or during a procedure. We'll see if it holds...

Answer 3

I have a partial reply for the Dell users, and for your formatting problem

The formatting and display problem is another known-but-undocumented issue in Excel.

Many flat-panel monitors (including laptop displays) are unable to render fonts correctly in textbox controls on an Excel spreadsheet: you've got a mild version of this problem.

Our company has recently upgraded to new (and much larger!) monitors, and I can at last use textboxes, labels and combo boxes in worksheets. Our old Samsung screens displayed text controls correctly, but any manual or VBA-driven updates resulted in an illegible jumble of overlapping characters.

Listboxes don't have the problem: it's the 'textbox' part of your combo box that has the issue. Try manipulating a listbox in VBA event procedures: it's a kludge but it works.

In-Cell dropdowns from Data Validation lists don't have the problem. If you set up a validation list for a cell, then set the data validation error messages to empty strings, you can enter free-form text in the cell; the drop-down list is advisory, not a mandatory limit-to-list.

The problem is sometimes ameliorated (but never completely fixed) by using the Terminal or System fonts in your Active-X control.

The problem is sometimes ameliorated (but never completely fixed) by using a VBA event to nudge or resize your Active-X control by 0.75 mm.

Check if your laptop manufacturer has released an upgrade to the display drivers.

...And that's everything I know about the font rendering problem. If Mike (with his Dell laptop) is reading this: Good luck with those workarounds - to the best of my knowledge, there's no real 'fix'.

The stability problem was a major headache for me until Excel 2003 came out: using any Active-X control in the sheet was a source of instability. The Jury's still out on Listbox controls embedded in a sheet, even in Excel 2003: I still avoid using them.