Errors in Excel ActiveX combo boxes?

Question

I noticed that I get all kinds of annoying errors when:

• I have ActiveX summaries in a worksheet (and not in Excel form)
• Combobox has an event code associated with them (e.g. 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 target cell.

I am programming a rather sophisticated excel application that does a ton of event processing and uses many controls. For several months I tried to deal with a lot of errors related to these lists. I can’t remember all the details of each instance now, but these errors usually include specifying the listfillrange and linkedcell properties in the named ranges and often relate to events with the list being launched at inopportune times (for example, when application.enableevents = false) . These problems seemed to get bigger in Excel 2007, so I had to completely abandon these combo boxes (now I use combo boxes contained in custom forms, and not directly on the sheet).

Has anyone else seen similar problems? If so, was there an elegant solution? I looked through Google and still have not noticed anyone with similar problems.

Some of the symptoms that I see are as follows:

• Excel crashes on startup (includes combobox_onchange, listfillrange-> named range on another different worksheet and workbook_open interactions). (note, I also did some validation of the data in the linked cells in case the user edited them directly.)
• Excel rendering errors (usually when a combo box changes, some cells from another sheet get randomly at the top of the current sheet). Sometimes this means that the screen blinks completely on another sheet for a moment.
• Excel passed out (more precisely, the call stack) (referring to the first point of the marker). Sometimes, when a function changes the comboboxes property, a combobox onchange event occurs, but it never returns control to the function that caused the change in the first place. Combobox_onchange events are fired even if application.enableevents = false.
• Burning events when they shouldn't (I sent another stack overflow question related to this).

At this point, I'm pretty sure that ActiveX comboboxs are embodied and not worth the trouble. Instead, I included these combo boxes inside a custom form module. I would prefer inconvenience to pop-up users than random visual artifacts and crash (data loss).

Answer 1

I do not have a definitive answer for you, but I can tell you that I stopped using the ListFillRange and LinkedCell controls for ActiveX about 10 years ago. I don’t remember what specific problems I encountered. I just remember that I came to the conclusion that no matter how little time they save me, there is no pain in the brain, trying to track down errors. So, now I populate the controls through the code and process the output in events.

Answer 2

My combo active-x block works fine when my Dell is docked, but it resizes to a larger font every time it is clicked when Dell is undocked - very strange. I added resizing code that works when detaching, but both .hight and .scaleheight do not work when docked and when started programmatically (even a stranger).

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

Then I added my own switch, similar to activations, so resizing only happens when the user selects the combobox value, and not when something is touched during the macro operation.

 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, it seems to work, whether docked or undocked, whether called by the user or during a procedure. Let's see if he persists ...

Answer 3

I have a partial answer for Dell users and for your formatting problem

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

Many flat-panel monitors (including laptop displays) cannot correctly display fonts in text box controls in an Excel spreadsheet: you have a moderate version of this problem.

Our company has recently been updated to new (and much more!) Monitors, and I can finally use text fields, labels and combined fields in the sheets. Our old Samsung screens displayed text controls correctly, but any updates related to the manual or VBA resulted in indiscriminate mixing of overlapping characters.

There is no problem with the lists: this is the "text box" field in your combo box that has a problem. Try manipulating the list in VBA event procedures: this is kludge, but it works.

In-cell drop-down lists from data validation lists do not have this problem. If you have set up a check list for a cell, then set data verification error messages on blank lines, you can enter free-form text in the cell; a drop-down list is a recommendation, not a mandatory limit for the list.

The problem is sometimes improved (but not completely fixed) using terminal or system fonts in your Active-X control.

The problem is sometimes improved (but not completely fixed) using the VBA event to push or resize the Active-X control by 0.75 mm.

Make sure your laptop manufacturer releases a display driver update.

... And all that I know about the problem of font rendering. If Mike (with his Dell laptop) reads this: Good luck with these workarounds - as far as I know, there are no real “fixes”.

The stability issue was a major headache for me until Excel 2003 appeared: using any Active-X control in a worksheet was a source of instability. The jury is still working on Listbox controls built into the worksheet, even in Excel 2003: I still don't use them.

Answer 4

So, I faced the same problems. I had a drop-down file that I put combobox on to deal with the illegibility problem by zooming too much. This was my code as 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 had to deal with all kinds of problems, but as mentioned above in this article, LinkedCell was the biggest problem. My choice from the drop-down menu will go everywhere where on the sheet I clicked on last, instead of the cell in which I selected the drop-down box, and in the process also violates the code where the choice will go. I used the simple ONE LINE code to make sure that my program in ActiveX only works if there is a drop-down menu. I used this before executing the LinkedCell command:

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

So now my code 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 

Incredibly, it worked. And now my excel sheet no longer leads to errors. Hope this helps.

Answer 5

For this reason, I use cells with data validation lists when placing combined fields in a spreadsheet.