如何防止VBA中的ActiveX事件触发？ [英] How to prevent ActiveX events firing in VBA?

问题描述

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

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

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

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

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

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组合框，将以下内容添加到该表单模块中

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

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

推荐答案

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

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

尝试这个

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

之前/之后ScreenShots：

关注评论：

另外，这样做会将硬核分解在一起的对象上，但是我可以取消组合对象（我们猜测它们不再是Sheet1.OLEobjects）。我仍然不喜欢这个，因为它依赖于这个事实，有时我会想要组合对象.. - 恩德兰17分钟前

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

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

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.

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

这篇关于如何防止VBA中的ActiveX事件触发？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！