EventsEnabled button

[mdmackillop]

I never make toolbar buttons by code, so I'll ask the experts!
When creating/debugging code, I may often set EnableEvents to False and then exit before resetting to true. how about a button which is innocuous when events are enabled, but is changed to a bright red alarm (or similar) otherwise. The operating function of the button is only to reset EnableEvents to True.

[Ken Puls]

Hi Malcolm,

What about just adding a button to the main file menu bar when events are disabled? Would that work for you?

[mdmackillop]

Hi Ken,
Something like that, but with a changing appearance to make clear the status.

[Ken Puls]

How's this?

[vba]Option Explicit

Const strButtonCaption = "Event Alerter"

Sub DisableEvents()
'Call instead of Application.EnableEvents = False

Dim cmdBar As CommandBarButton

Application.EnableEvents = False
On Error Resume Next
Set cmdBar = Application.CommandBars("Worksheet Menu Bar").Controls(strButtonCaption)
If Err.Number <> 0 Then
Call CreateButton
Set cmdBar = Application.CommandBars("Worksheet Menu Bar").Controls(strButtonCaption)
End If
On Error GoTo 0
cmdBar.FaceId = 352

End Sub

Sub RemoveEventsButton()
'Call from Workbook_BeforeClose
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls(strButtonCaption).Delete

End Sub

Sub EnableEvents()
'Call instead of Application.EnableEvents = True

Dim cmdBtn As CommandBarButton

Set cmdBtn = Application.CommandBars("Worksheet Menu Bar").Controls(strButtonCaption)
cmdBtn.FaceId = 342
Application.EnableEvents = True

End Sub

Sub CreateButton()
'Call from Workbook_Open Event

Dim cmdBtn As CommandBarButton

With Application.CommandBars("Worksheet Menu Bar")
Set cmdBtn = .Controls.Add(Type:=msoControlButton, ID:=2950, Before:=.Controls.Count + 1)
With cmdBtn
.OnAction = "EnableEvents"
.FaceId = 342
.Caption = strButtonCaption
End With
End With

End Sub[/vba]

[mdmackillop]

Very nice Ken
Thank you.

[Bob Phillips]

I was thinking along the same lines as Ken, I even came up with the same icon, but I thought of checking events periodically, and allowing event5s to be set as usual, so I came up with this

[vba]

Option Explicit

Public Const EVENT_NOTIFIER As String = "EventNotifier"
Public Const EVENT_CAPTION As String = "EventsLight"
Public Const EVENT_CHECK_DELAY As Long = 5 'secs
Public nTime As Double

Sub AddCommandbar()
Call RemoveCommandbar

With Application.CommandBars.Add(Name:=EVENT_NOTIFIER, temporary:=True)

With .Controls.Add(Type:=msoControlButton)

.Caption = EVENT_CAPTION
Call EventTimer
.OnAction = "ToggleEvents"

End With

.Visible = True

End With

End Sub

Sub RemoveCommandbar()

On Error Resume Next

Application.CommandBars(EVENT_NOTIFIER).Delete

On Error GoTo 0

End Sub

Sub ToggleEvents()

With Application.CommandBars.ActionControl

If Application.EnableEvents Then

Application.EnableEvents = False
.FaceId = 352
.TooltipText = "Events Disabled"

Else

Application.EnableEvents = True
.FaceId = 351
.TooltipText = "Events Enabled"

End If

End With

End Sub

Sub EventTimer()

With Application.CommandBars(EVENT_NOTIFIER).Controls(EVENT_CAPTION)

If Application.EnableEvents Then
.FaceId = 351
.TooltipText = "Events Enabled"
Else
.FaceId = 352
.TooltipText = "Events Disabled"
End If

End With

nTime = Now + TimeSerial(0, 0, EVENT_CHECK_DELAY)
Application.OnTime nTime, "EventTimer"

End Sub
[/vba]

[Ken Puls]

I even came up with the same icon

That's funny!

Actually, I tried to get it to delete the button when you pressed it, but I guess you can't delete it if it's still running the OnAction event.

[Bob Phillips]

That's funny!

Actually, I tried to get it to delete the button when you pressed it, but I guess you can't delete it if it's still running the OnAction event.

Issue a delete within an OnTime procedure.

[Ken Puls]

Sorry, should have said. I opted not to do that because it feels... I dunno... clunky. I'm not a big fan of farming stuff out to OnTime if I can avoid it.

Having said that, I actually quite like the idea of the check you put in which, of course, requires OnTime to work. The only issue I see there is if you have a really long procedure that takes forever to run (a really badly constructed loop that we would never use, for example), you might turn on events when you don't want them. Or course, you could set the timer longer too, but it is something to be aware of.

[Bob Phillips]

My OnTime code only checks events, it doesn't change the status in any way. My button can toggle events, but that is separate, and clearly indicated by the bulb colour.

BTW I know what you mean about using OnTime, but I have to admit to finding it as one of the handiest methods in VB.