Macro Button that deactivates/activates worksheet change events

[coach_veto]

Hi guys,

I have a worksheet with a long list of worksheet change events.

I am trying to create a toggle, preferably a button on the worksheet, that will deactivate all the change event code when clicked, but then activate the same code if clicked again and so on...

The purpose is so users of this template I'm working on are able to use the worksheet without the change events working if they don't want them to. Kind of like flipping a switch.

Any ideas regarding the type of code required or how to go about this?

Thanks!

 

[Norie]

You could do it with one button or even a toggle button.

Add a Forms button to the worksheet, give it a caption like 'Deactivate Events', right click it, select Assign Macro... and click New.

You should now see something like this.

Sub Button1_Click()
    
End Sub

This is where the code goes, and here's the code.

Sub Button1_Click()
Dim btn As Object

    Set btn = Sheets("Sheet1").Shapes(Application.Caller)
    
    If btn.OLEFormat.Object.Caption = "Deactivate Events" Then
        Application.EnableEvents = False
        btn.OLEFormat.Object.Caption = "Activate Events"
    Else
        Application.EnableEvents = True
        btn.OLEFormat.Object.Caption = "Deactivate Events"
    End If
    
End Sub