vba Excel 的 ActiveX 组合框中的错误？

Bugs in Excel's ActiveX combo boxes?

提问by Kimball Robinson

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.

• 我在工作表上有 ActiveX 组合框（不是 Excel 表单）
• 组合框具有链接到它们的事件代码（例如，onchange 事件）
• 我使用它们的 listfillrange 或 linkscell 属性（清除这些属性似乎可以缓解很多问题）
• （不确定这是否已连接）但目标链接单元上有数据验证。

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).

我编写了一个相当复杂的 excel 应用程序，它执行大量事件处理并使用大量控件。几个月来，我一直在尝试处理与这些组合框有关的各种错误。我现在不记得每个实例的所有细节，但这些错误往往涉及将 listfillrange 和 linkscell 属性指向命名范围，并且通常与在不适当的时间触发的组合框事件有关（例如当 application.enableevents = 假）。这些问题在 Excel 2007 中似乎越来越大，因此我不得不完全放弃这些组合框（我现在使用包含在用户表单中的组合框，而不是直接在工作表上）。

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).

• 启动时 Excel 崩溃（涉及组合框_onchange、listfillrange->另一个不同工作表上的命名范围以及 workbook_open 交互）。（注意，我还对链接的单元格进行了一些数据验证，以防用户直接编辑它们。）
• Excel 呈现错误（通常当组合框更改时，另一个工作表中的某些单元格会随机绘制在当前工作表的顶部）有时它涉及屏幕完全闪烁到另一个工作表片刻。
• Excel 失去理智（或者更确切地说，调用堆栈）（与第一个要点相关）。有时，当函数修改组合框的属性时，组合框 onchange 事件会触发，但它永远不会将控制权返回给首先导致更改的函数。即使在 application.enableevents = false 时，combobox_onchange 事件也会被触发。
• 不应该触发的事件（我在与此相关的堆栈溢出上发布了另一个问题）。

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).

在这一点上，我相当确信 ActiveX 组合框是邪恶的化身，不值得麻烦。我已经转而将这些组合框包含在用户表单模块中。我宁愿用弹出式表单给用户带来不便，也不愿出现随机的视觉伪影和崩溃（数据丢失）。

采纳答案by Dick Kusleika

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.

我没有明确的答案，但我可以告诉你，大约 10 年前，我停止将 ListFillRange 和 LinkedCell 用于 ActiveX 控件。我不记得我遇到了什么特别的问题。我只记得我得出的结论是，无论他们为我节省了多少时间，都不值得为追查错误而头疼。所以现在我通过代码填充控件并处理事件中的输出。

回答by mike

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).

当我的戴尔停靠时，我的 active-x 组合框工作正常，但每次在戴尔未停靠时单击它都会调整为更大的字体 - 非常奇怪。我添加了调整大小的代码，该代码在未停靠时有效，但是 .height 和 .scaleheight 在停靠和以编程方式触发时都失败（甚至更奇怪）。

        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.

然后我添加了我自己的类似 enableevents 的开关，这样调整大小只在用户选择组合框值时发生，而不是在宏运行时发生任何影响时。

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...

最后，无论是停靠还是未停靠，无论是由用户触发还是在程序期间，这似乎都有效。我们会看看它是否成立...

回答by Nigel Heffernan

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.

格式和显示问题是 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.

许多平板显示器（包括笔记本电脑显示器）无法在 Excel 电子表格的文本框控件中正确呈现字体：您遇到了这个问题的温和版本。

Our company has recently upgraded to new (and much larger!) monitors, and I can at lastuse 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.

我们公司最近升级到新的（而且更大！）显示器，我终于可以在工作表中使用文本框、标签和组合框了。我们的旧三星屏幕正确显示文本控件，但任何手动或 VBA 驱动的更新都会导致难以辨认的重叠字符混乱。

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.

列表框没有问题：是组合框的“文本框”部分有问题。尝试在 VBA 事件过程中操作一个列表框：这是一个杂七杂八的东西，但它有效。

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.

数据验证列表中的 In-Cell 下拉菜单没有问题。如果为单元格设置了验证列表，然后将数据验证错误消息设置为空字符串，则可以在单元格中输入自由格式的文本；下拉列表是建议性的，而不是强制性的限制列表。

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

通过在 Active-X 控件中使用终端或系统字体，该问题有时会得到改善（但从未完全解决）。

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.

通过使用 VBA 事件将 Active-X 控件微调或调整 0.75 毫米，有时会改善（但从未完全修复）该问题。

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.

在 Excel 2003 出现之前，稳定性问题一直困扰着我：在工作表中使用任何 Active-X 控件都是不稳定的根源。即使在 Excel 2003 中，陪审团仍然无法使用嵌入在工作表中的 Listbox 控件：我仍然避免使用它们。

回答by Sampat Kedarisetty

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:

我面临着各种各样的问题，但正如在此线程中主要提到的，LinkedCell 问题是最大的。我从下拉菜单中的选择会出现在我上次单击的工作表上的任何位置，而不是我从中选择下拉框的单元格，并且在此过程中，还会干扰选择所在位置的代码。我使用了一个简单的 ONE LINE 代码来确保我的 ActiveX 程序仅在其下拉菜单时运行。我在 LinkedCell 命令运行之前使用了它：

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.

令人难以置信的是，这奏效了。现在我的 excel 表不再行为不端了。希望这可以帮助。

回答by guitarthrower

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

出于这个原因，我在将组合框放在电子表格上时使用带有数据验证列表的单元格。