如何防止在 VBA 中触发 ActiveX 事件？

How to prevent ActiveX events firing in VBA?

提问by enderland

I am searching for a better way to disable ActiveX events from triggering in an Excel workbook (though this would apply to all Office apps with ActiveX objects).

我正在寻找一种更好的方法来禁止在 Excel 工作簿中触发 ActiveX 事件（尽管这适用于所有带有 ActiveX 对象的 Office 应用程序）。

Hopefully something similar to Application.EnableEvents = false, though this does not work with ActiveX.

希望类似于Application.EnableEvents = false，尽管这不适用于 ActiveX。

In the below example it's trivial to use a global boolean but I have a lot of event handlers for my ActiveX objects and it would be immensely easier for something I could universally apply to temporarily disable ActiveX events. I don't really want to add an if/exit sub statement to every single one of these methods.

在下面的示例中，使用全局布尔值是微不足道的，但我的 ActiveX 对象有很多事件处理程序，对于我可以普遍应用以暂时禁用 ActiveX 事件的东西来说，这将非常容易。我真的不想为这些方法中的每一个添加 if/exit 子语句。

To demonstrate this problem, create an ActiveX combobox in a worksheet and add the following to that sheet module

为了演示这个问题，在工作表中创建一个 ActiveX 组合框并将以下内容添加到该工作表模块

Public initializingContent As Boolean 
Private Sub intializeAllActiveXContent()

    'this doesn't apply to activeX events :'(
    Application.EnableEvents = False

    'this could work but is not really elegant
    'change this to false to show my problem in 
    'the intermediate window (called not once but twice)
    initializingContent = True

    ComboBoxTest.Clear

    ComboBoxTest.AddItem ("item1")
    ComboBoxTest.AddItem ("item2")
    ComboBoxTest.AddItem ("item3")

    'select the top value in the box
    ComboBoxTest.value = "item1"

    initializingContent = False

    Application.EnableEvents = True

     End Sub

Private Sub ComboBoxTest_Change()
    'I really don't want to have to wrap EVERY single ActiveX method
    'with something like this for a whole variety of reasons...
    If initializingContent Then Exit Sub

    Debug.Print "do stuff I don't want to happen when intializeAllActiveXContent() runs " & _
        "but I do when user changes box"
         End Sub

采纳答案by Siddharth Rout

Why not disable them? That ways you don't have to worry about their individual codes as well.

为什么不禁用它们？这样您就不必担心他们的个人代码。

Try this

尝试这个

Sub DisableActiveXControls()
    Dim ws As Worksheet
    Dim OLEobj As OLEObject

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        For Each OLEobj In ws.OLEObjects

        If TypeOf OLEobj.Object Is MSForms.ComboBox Then
            OLEobj.Enabled = False
        End If
        Next OLEobj
    End With
End Sub

Before/After ScreenShots:

截图之前/之后：

FOLLOWUP FROM COMMENTS:

来自评论的跟进：

Also turns out this breaks hard core on objects which are grouped together but I can ungroup objects (they are no longer in "Sheet1.OLEobjects" I guess). I still don't really like this since it relies on this fact and there will be times when I do want to group objects.. – enderland 17 mins ago

事实证明，这会破坏组合在一起的对象的核心，但我可以取消对象的组合（我猜它们不再在“Sheet1.OLEobjects”中）。我仍然不太喜欢这个，因为它依赖于这个事实，有时我确实想对对象进行分组.. – enderland 17 分钟前

To disables ActiveX Controls in a group, you don't need to ungroup them. Use this code. The below code will disable Comboboxes in a group.

要禁用组中的 ActiveX 控件，您无需取消它们的组合。使用此代码。以下代码将禁用组中的组合框。

Sub Disable_ActiveX_Controls_In_A_Group()
    Dim shp As Shape, indvShp As Shape
    Dim OLEobj As OLEObject
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet1")

    For Each shp In ws.Shapes
        If shp.Type = msoGroup Then
            For Each indvShp In shp.GroupItems
                Set objOLE = indvShp.OLEFormat.Object

                If objOLE.progID = "Forms.ComboBox.1" Then _
                objOLE.Enabled = False
            Next
        End If
    Next
End Sub

回答by Waz J

I know this is really old. But anyone who looks this up (first hit on google) might want a simple answer:

我知道这真的很老了。但是任何查找这个（第一次在谷歌上搜索）的人都可能想要一个简单的答案：

Lets say you have a Private Sub ActiveXControl_Change() that is getting called during an Application.EnableEvents = False and you want it to skip this just go:

假设您有一个 Private Sub ActiveXControl_Change() 在 Application.EnableEvents = False 期间被调用，并且您希望它跳过这个就行了：

Private Sub ActiveXControl_Change()
If Application.EnableEvents = True Then
'enter you code here

End If
End Sub

回答by Michael Fox

came across this post when searching for a similar issue. I decided the answer posted here would not suit my situation, and came up with something else. My approach may be useful here also.

在搜索类似问题时遇到了这篇文章。我认为这里发布的答案不适合我的情况，并想出了其他办法。我的方法在这里也可能有用。

In my workbook, I have a set of 20 (Activex) checkboxes. These essentially filter on/off 20 categories of product to be included in a report. There is a routine that runs each time one of these checkboxes is changed, simply by calling the routing from the CheckBox_Click routine.

在我的工作簿中，我有一组 20 个 (Activex) 复选框。这些基本上过滤了要包含在报告中的 20 类产品。每次更改这些复选框时都会运行一个例程，只需从 CheckBox_Click 例程调用路由即可。

Then I also have a combobox that selects various groupsof these checkboxes. The trouble is that when EACH of the 20 checkboxes is (un)selected by the combobox code, the calculation routine is triggered.

然后我还有一个组合框，可以选择这些复选框的各个组。问题在于，当组合框代码（取消）选择了 20 个复选框中的每一个时，就会触发计算例程。

What I wanted was to run the calculation routine only ONCEat the end of the combobox, but still have it work if I change an individual checkbox manually.

我想要的是只能运行计算例程ONCE在组合框的结束，但仍然有它的工作，如果我手动更改单个复选框。

My solution - put in a TRUE/FALSE code in some cell on a hidden worksheet, somewhere it's out of the way and won't interfere.

我的解决方案 - 在隐藏工作表上的某个单元格中放入一个 TRUE/FALSE 代码，在某个不碍事的地方，不会干扰。

Then the CheckBox_Click code becomes (for each of the 20 checkboxes):

然后 CheckBox_Click 代码变为（对于 20 个复选框中的每一个）：

Private Sub CheckBox6_Click()
If (Sheets("Data").Range("G60")) Then
    Call RangeFind
End If
End Sub

The combobox code modifies this same cell, appropriately:

组合框代码适当地修改了同一个单元格：

Private Sub ComboBox28_Change()

Sheets("Data").Range("G60").Value = False
Select Case Sheets("Data").Range("F50").Value
    Case "1"

    CheckBox1.Value = False
    CheckBox2.Value = False
    CheckBox3.Value = False

....

End Select

'  This turns the checkboxes back on
Sheets("Data").Range("G60").Value = True

'  This now actually calls the calculation ONCE
Call RangeFind

ActiveSheet.Range("A1").Activate

End Sub

Cheers,

干杯，

Michael

迈克尔

回答by Allen

Here is one option:

这是一种选择：

Private Sub ToggleButton1_Click()
    If Application.EnableEvents = True Then
        'This next line forces at least 1 of the toggle's to be selected at all 
        'times; remove it from all routines if this is not desired.
        If Not ToggleButton1.Value Then ToggleButton1.Value = True
        Application.EnableEvents = False
        ToggleButton2.Value = False
        ToggleButton3.Value = False
        Application.EnableEvents = True
    End If
End Sub

Private Sub ToggleButton2_Click()
    If Application.EnableEvents = True Then
        If Not ToggleButton2.Value Then ToggleButton2.Value = True
        Application.EnableEvents = False
        ToggleButton1.Value = False
        ToggleButton3.Value = False
        Application.EnableEvents = True
    End If
End Sub

Private Sub ToggleButton3_Click()
    If Application.EnableEvents = True Then
        If Not ToggleButton3.Value Then ToggleButton3.Value = True
        Application.EnableEvents = False
        ToggleButton1.Value = False
        ToggleButton2.Value = False
        Application.EnableEvents = True
    End If
End Sub