event that cannot be disabled?

  • Thread starter Thread starter oldyork90
  • Start date Start date
O

oldyork90

I turn events off and then back on... usually.

If I fail to re-enable them, is there an event that persists? Is there an event that will fire every time?

I ask because I have to set something up to allow the user to re-enable events I messed up. (or screen updating, or automatic cal ...)

Thank you
 
Hi,

Am Mon, 24 Feb 2014 06:28:41 -0800 (PST) schrieb (e-mail address removed):
I turn events off and then back on... usually.

If I fail to re-enable them, is there an event that persists? Is there an event that will fire every time?

I ask because I have to set something up to allow the user to re-enable events I messed up. (or screen updating, or automatic cal ...)
Click to expand...

if you set EnableEvents to false all events are disabled.
If your macro can fail try it with an errorhandler that events will be
enabled, e.g.:

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

On Error GoTo EventsOn

' your code

EventsOn:
With Application
.ScreenUpdating = True
.EnableEvents = True
End With


Regards
Claus B.
 
In addition to Claus' good advice to impliment an error handler in your
complex routines, it sounds like you need a methodology in place to
properly manage Excel events!

I use the following mechanism to ensure only 1 routine has control of
Excel's events until that routine is done with them. This ensures
another routine doesn't inadvertently toggle these setting mid-code in
the original caller, AND that events aren't inadvertently left turned
off.

Just drop this into a standard module and reuse for all your
projects...

Sub EnableFastCode(Caller$, Optional SetFast As Boolean = True)
'The following will make sure only the Caller has control,
'and allows any Caller to take control when not in use.
If AppMode.CallerID <> Caller Then _
If AppMode.CallerID <> "" Then Exit Sub

With Application
If SetFast Then
AppMode.Display = .ScreenUpdating
.ScreenUpdating = False
AppMode.CalcMode = .Calculation
.Calculation = xlCalculationManual
AppMode.Events = .EnableEvents
.EnableEvents = False
AppMode.CallerID = Caller
Else
.ScreenUpdating = AppMode.Display
.Calculation = AppMode.CalcMode
.EnableEvents = AppMode.Events
AppMode.CallerID = ""
End If
End With
End Sub 'EnableFastCode

...which requires the following 'Type' declaration to work correctly.

Type udtAppModes
Events As Boolean
CalcMode As XlCalculation
Display As Boolean
CallerID As String
End Type
Public AppMode As udtAppModes

To use the procedure I just call it from any def and pass the args as
needed...

Sub MySub()
Const sSource$ = "MySub"
On Error GoTo errExit
EnableFastCode sSource '//turn it on
'...code follows

errExit:
EnableFastCode sSource, False ''//turn it off
End Sub 'MySub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
In addition to Claus' good advice to impliment an error handler in your

complex routines, it sounds like you need a methodology in place to

properly manage Excel events!
Click to expand...
I never even thought of doing it that way. I will copy and credit. Thank you.
(do you guys get paid by uS to help out?)

my old way:

Function disableEvents() As Boolean
disableEvents = Application.enableEvents
Application.enableEvents = False
End Function

Sub enableEvents(Optional previous_event_status As Boolean = True)
Application.enableEvents = previous_event_status
End Function
 
Back
Top