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.

Answer 4

So I was facing the same issues. I had a file with drop down lists on which I had superimposed the combobox to fight the issue of illegibility when zooming out too much. This was what my code looked like INITIALLY:

'=========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
On Error GoTo errHandler

If Target.Count > 1 Then GoTo exitHandler

Set cboTemp = ws.OLEObjects("ComboBox1")
  On Error Resume Next
If cboTemp.Visible = True Then
  With cboTemp
    .Top = 10
    .Left = 10
    .ListFillRange = "Treatment"
    .LinkedCell = Target.Address
    .Visible = False
    .Value = ""
  End With
End If



  On Error GoTo errHandler
  If Target.Validation.Type = 3 Then
    'if the cell contains a data validation list
    Application.EnableEvents = False
    'get the data validation formula
    str = Target.Validation.Formula1
    str = Right(str, Len(str) - 1)
    With cboTemp
      'show the combobox with the list
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 15
      .Height = Target.Height + 5
      .ListFillRange = ws.Range(str).Address
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
    'open the drop down list automatically
    Me.ComboBox1.DropDown
  End If

exitHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Exit Sub
errHandler:
  Resume exitHandler

End Sub
'====================================
'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
'***NOTE: if KeyDown causes problems, change to KeyUp
'Table with numbers for other keys such as Right Arrow (39)
'https://msdn.microsoft.com/en-us/library/aa243025%28v=vs.60%29.aspx

Private Sub ComboBox1_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
    Select Case KeyCode
        Case 9 'Tab
            ActiveCell.Offset(0, 1).Activate
        Case 13 'Enter
            ActiveCell.Offset(1, 0).Activate
        Case Else
            'do nothing
    End Select
End Sub
'====================================

I was facing all sorts of issues but as primarily mentioned on this thread, the LinkedCell issue was the biggest. My selection from the drop down menu would go wherever on the sheet I had clicked last, instead of the cell I had chosen the drop down box from, and in process, also disturbing the code of wherever the selection would go. I used a simple ONE LINE code to make sure my program in ActiveX runs only when its a drop down menu. I used this before the LinkedCell command ran:

If Target.Validation.Type = 3 Then
'... all the normal code here...
End If

So my code now looks like this:

'... Code as before

If Target.Validation.Type = 3 Then
' NEW CODE LINE ABOVE
 If Target.Count > 1 Then GoTo exitHandler

Set cboTemp = ws.OLEObjects("ComboBox1")
  On Error Resume Next
If cboTemp.Visible = True Then
  With cboTemp
    .Top = 10
    .Left = 10
    .ListFillRange = "Treatment"
    .LinkedCell = Target.Address
    .Visible = False
    .Value = ""
  End With
End If
End If
' End of the new If

Unbelievably, this worked. And now my excel sheet isn't misbehaving anymore. Hope this helps.

Answer 5

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