Toggle Events in Excel Options

[Mat]

Hi,

Is there a way to toggle the Events somewhere in the options of Excel, without using VBA code?

 

[schielrn]

Not sure I follow what you mean by toggle events?

 

[Norie]

If you mean Application.EnableEvents... then the short answer is no.

You could I suppose create a command button on a toolbar to toggle them on and off.

In fact I've never thought of that - it might be handy.

 

[Mat]

If you mean Application.EnableEvents... then the short answer is no.

You could I suppose create a command button on a toolbar to toggle them on and off.

In fact I've never thought of that - it might be handy.

Yes that exactly what I meant...

I was trying to avoid adding button (either on a toolbar or on the sheet), 'cause this mean publishing a new version of my Workbook to people at work, instead of just telling them how to toggle events using Excel options (which is more easier to explain than how to add a macro with the code Application.EnableEvents = True and run it, plus this would have to be in each copy of the workbook).

Well, guess I have no choices.

Thanks for the answer

 

[Smitty]

Check out Chip Pearson's site (www.cpearson.com) for "Programming to the VBE". It's possible to create an update workbook you e-mail to people, which when opened can add a new module to the end user's wb for you (and a button if you wanted).

HTH,

 

[Norie]

Well I've not created a custom command button for awhile.

Option Explicit
 
Sub AddButton()
Dim cbbtn As CommandBarButton
 
    Set cbbtn = Application.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlButton, ID:=2950, Before:=13)
 
    cbbtn.TooltipText = "App Events On"
    cbbtn.OnAction = "ToggleEvents"
    
    cbbtn.Tag = "ToggleEvents"
 
End Sub
 
Sub ToggleEvents()
Dim cbbtn As CommandBarButton
    Set cbbtn = Application.CommandBars.FindControl(Tag:="ToggleEvents")
    
    Select Case cbbtn.FaceId

        Case 2950
            cbbtn.FaceId = 276

            cbbtn.TooltipText = "App Events Off"

            Application.EnableEvents = False

        Case 276
            cbbtn.FaceId = 2950

            cbbtn.TooltipText = "App Events On"

            Application.EnableEvents = True

    End Select
    
End Sub

This code comes with a big disclaimer - remember it turns events off and on which might not be too desirable.*

It does come with a smiley face for on and a sad face for off though.

 

[sspatriots]

Good morning, I realize this code is old, but could someone tell me where to place the solution above in my workbook. I'm guessing part of it goes in "This Workbook" and the other part might go in a regular module. Also, is it saying I just create a button and assign one of those two macros to it? I like the idea, just need to know how to use it. Thanks