Hi,
I'm using XL2007SP2 on WinXP and I'm having problems with the
following code:

Sub Turn_Off()
With Application
.EnableEvents = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
End Sub

When I run it, the screen updating stays set to true, while the other
two commands change condition. Can anyone tell what I need to do to
fix it?
Thanks.
James

After serious thinking John Smith wrote :
Hi,
I'm using XL2007SP2 on WinXP and I'm having problems with the
following code:

Sub Turn_Off()
With Application
.EnableEvents = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
End Sub

When I run it, the screen updating stays set to true, while the other
two commands change condition. Can anyone tell what I need to do to
fix it?
Thanks.
James

ScreenUpdating is not persistent and so automatically turns back on
when your procedure ends. It only works while the procedure runs!

--
Garry

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

When you run code from the VBE that is what you get.
Close the VBE and run the code and see if it kicks in.
'---
An extra... make sure that .EnableEvents is set back to true before exiting your code.
That includes setting it to true in your error handler.
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)



"John Smith"
wrote in message
...
Hi,
I'm using XL2007SP2 on WinXP and I'm having problems with the
following code:

Sub Turn_Off()
With Application
.EnableEvents = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
End Sub

When I run it, the screen updating stays set to true, while the other
two commands change condition. Can anyone tell what I need to do to
fix it?
Thanks.
James

On Feb 23, 4:12*pm, "Jim Cone" wrote:
When you run code from the VBE that is what you get.
Close the VBE and run the code and see if it kicks in.
'---
An extra... make sure that .EnableEvents is set back to true before exiting your code.
That includes setting it to true in your error handler.
'---
Jim Cone
Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)

"John Smith"
wrote in ...



Hi,
I'm using XL2007SP2 on WinXP and I'm having problems with the
following code:

Sub Turn_Off()
With Application
* *.EnableEvents = False
* *.ScreenUpdating = False
* *.Calculation = xlCalculationManual
End With
End Sub

When I run it, the screen updating stays set to true, while the other
two commands change condition. Can anyone tell what I need to do to
fix it?
Thanks.
James- Hide quoted text -

- Show quoted text -

Thank you, gentlemen. You have set me straight. Yes, Jim, I have a
similar module that resets everything after the procedure.
James

John Smith used his keyboard to write :
On Feb 23, 4:12*pm, "Jim Cone" wrote:
When you run code from the VBE that is what you get.
Close the VBE and run the code and see if it kicks in.
'---
An extra... make sure that .EnableEvents is set back to true before exiting
your code. That includes setting it to true in your error handler.
'---
Jim Cone
Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)

"John Smith"
wrote in
...



Hi,
I'm using XL2007SP2 on WinXP and I'm having problems with the
following code:

Sub Turn_Off()
With Application
* *.EnableEvents = False
* *.ScreenUpdating = False
* *.Calculation = xlCalculationManual
End With
End Sub

When I run it, the screen updating stays set to true, while the other
two commands change condition. Can anyone tell what I need to do to
fix it?
Thanks.
James- Hide quoted text -

- Show quoted text -

Thank you, gentlemen. You have set me straight. Yes, Jim, I have a
similar module that resets everything after the procedure.
James

This should be handled in the same procedure so your caller (procedure
using it) can control how it gets reset. Here's how I do it...

Public Sub EnableFastCode(Optional SetFast As Boolean = True, _
Optional EventsEnabled As Boolean = False, _
Optional CalcMode As Long = xlCalculationManual)
With Application
.EnableEvents = EventsEnabled
.Calculation = CalcMode
.ScreenUpdating = Not SetFast
End With 'Application
End Sub 'EnableFastCode

To use it...

Sub DoStuff()
Dim bEventsEnabled As Boolean, lCalcMode As Long

'Store existing setting
With Application
bEventsEnabled = .EnableEvents: lCalcMode = .Calculation
End With 'Application

EnableFastCode '..turns everything off
'..code to do stuff
'..code to do stuff
'..code to do stuff

'..code to do stuff
'..code to do stuff

'..code to do stuff
'..code to do stuff
'..code to do stuff

'..code to do stuff
'..code to do stuff

'..code to do stuff
'..code to do stuff
'..code to do stuff

'..code to do stuff
'..code to do stuff
EnableFastCode False, bEventsEnabled, lCalcMode
End Sub 'DoStuff

--
Garry

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

On Feb 23, 5:32*pm, GS wrote:
John Smith used his keyboard to write :





On Feb 23, 4:12*pm, "Jim Cone" wrote:
When you run code from the VBE that is what you get.
Close the VBE and run the code and see if it kicks in.
'---
An extra... make sure that .EnableEvents is set back to true before exiting
your code. That includes setting it to true in your error handler.
'---
Jim Cone
Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)

"John Smith"
wrote in
...

Hi,
I'm using XL2007SP2 on WinXP and I'm having problems with the
following code:

Sub Turn_Off()
With Application
* *.EnableEvents = False
* *.ScreenUpdating = False
* *.Calculation = xlCalculationManual
End With
End Sub

When I run it, the screen updating stays set to true, while the other
two commands change condition. Can anyone tell what I need to do to
fix it?
Thanks.
James- Hide quoted text -

- Show quoted text -

Thank you, gentlemen. You have set me straight. Yes, Jim, I have a
similar module that resets everything after the procedure.
James

This should be handled in the same procedure so your caller (procedure
using it) can control how it gets reset. Here's how I do it...

Public Sub EnableFastCode(Optional SetFast As Boolean = True, _
* * * * * * * * * * *Optional EventsEnabled As Boolean = False, _
* * * * * * * * * * *Optional CalcMode As Long = xlCalculationManual)
* With Application
* * .EnableEvents = EventsEnabled
* * .Calculation = CalcMode
* * .ScreenUpdating = Not SetFast
* End With 'Application
End Sub 'EnableFastCode

To use it...

Sub DoStuff()
* Dim bEventsEnabled As Boolean, lCalcMode As Long

* 'Store existing setting
* With Application
* * bEventsEnabled = .EnableEvents: lCalcMode = .Calculation
* End With 'Application

* EnableFastCode '..turns everything off
* '..code to do stuff
* '..code to do stuff
* '..code to do stuff

* '..code to do stuff
* '..code to do stuff

* '..code to do stuff
* '..code to do stuff
* '..code to do stuff

* '..code to do stuff
* '..code to do stuff

* '..code to do stuff
* '..code to do stuff
* '..code to do stuff

* '..code to do stuff
* '..code to do stuff
* EnableFastCode False, bEventsEnabled, lCalcMode
End Sub 'DoStuff

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup!
* * comp.lang.basic.visual.misc
* * microsoft.public.vb.general.discussion- Hide quoted text -

- Show quoted text -

Thanks, Garry. I'll give a try.