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 :

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 ...)

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Error GoTo EventsOn


Very good suggestion. Thanks.

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

On Monday, February 24, 2014 10:04:31 AM UTC-6, GS wrote:
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 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

(do you guys get paid by uS to help out?)

NO!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Monday, February 24, 2014 10:50:04 AM UTC-6, GS wrote:
(do you guys get paid by uS to help out?)



NO!



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

You should