Geek Answers Handbook

How to prevent ActiveX events from triggering in VBA?

I am looking for a better way to disable ActiveX events from running in an Excel workbook (although this applies to all Office applications with ActiveX objects).

Hope something similar to Application.EnableEvents = false, although this does not work with ActiveX.

In the example below it is trivial to use a global boolean, but I have many event handlers for my ActiveX objects, and it would be much easier for what I could universally use to temporarily disable ActiveX events. I really don't want to add an auxiliary if / exit statement for each of these methods.

To demonstrate this problem, create an ActiveX combo box on the worksheet and add the following to this 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
+4
3

? , .

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:

enter image description here

:

, ​​ , , ( "Sheet1.OLEobjects", ). , , , .. - enderland 17

ActiveX , . . Comboboxes .

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
+4

. , , , , - . .

20 (Activex) . / 20 , . , , , CheckBox_Click.

, . , , EACH 20 (un) combobox, .

, , , ONCE , , .

- TRUE/FALSE - , - .

CheckBox_Click ( 20 ):

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

:

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

,

+1

, . , ( google), :

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

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

End If
End Sub
+1
source

More articles:


All Articles