Hi,
I don't know why but the Application.ScreenUpdating=False does't work in VBA
Excel in a specific situation (lot of workbook open). I never had any problem
with this before.

Function A:
Dim bValue as boolean
bValue = Application.ScreenUpdating
Application.ScreenUpdating = False'this works
'Treatment
Application.ScreenUpdating= bValue... 'this works: the value return to True

into an other function thereafter, I have the line:
Application.ScreenUpdating = False
after the execution of this line, in the debug box:
debug.print Application.ScreenUpdating
True

When I open a new instance of Excel and open the workbook from the new
instance, it works well. Any idea?

Thank you!
Alex

--
Alex St-Pierre

Maybe Excel is getting confused with all those workbooks open, especially if
all those other WBs have lots of code. I can't explain it, but I've seen
some weird things like this....

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Alex St-Pierre" wrote:

Hi,
I don't know why but the Application.ScreenUpdating=False does't work in VBA
Excel in a specific situation (lot of workbook open). I never had any problem
with this before.

Function A:
Dim bValue as boolean
bValue = Application.ScreenUpdating
Application.ScreenUpdating = False'this works
'Treatment
Application.ScreenUpdating= bValue... 'this works: the value return to True

into an other function thereafter, I have the line:
Application.ScreenUpdating = False
after the execution of this line, in the debug box:
debug.print Application.ScreenUpdating
True

When I open a new instance of Excel and open the workbook from the new
instance, it works well. Any idea?

Thank you!
Alex

--
Alex St-Pierre

This can sometimes happen if you turn it off (set False) in a procedure your
code goes into break mode. It has no effect if running in break mode. So if
you are using F8 to step through your code it will appear to not be working.
Understand that in this case it's been disabled and so is normal behavior.

Some other things to know about using ScreenUpdating:
Besides hiding screen activity while code is running, that in itself makes
your code run faster because screen activity is suspended and so doesn't
require refreshing as code executes. Another setting that commonly
accompanies this is Calculation. Even though screen activity is temporarily
suspended, Excel is still calculating whenever cell values change. It's
better to turn that off until all the work your macro is doing is finished,
then turn it on and force a calculation so values update. (A definite 'plus'
when running long procedures or processing large amounts of data)

Excel always turns ScreenUpdating back on after the procedure that turned it
off ends. If this procedure was called from another procedure that also
turned it off then it may have no effect until that procedure ends or turns
it off, ..and so on up the call stack.

It's good practice to control turning it off/on as a 'wrapper' around lines
of code that you want to hide any screen activity that may occur while it's
executing. It's easy to lose track of when you have it on or off, or if Excel
turned it on when you wanted it off. It can get very confusing and makes
debugging a harder chore than necessary. Even though Excel will turn it on
when procedures end, you should get into the habit of controlling this in
your code before&after those code segments that make changes to the UI that
you want to hide until the task is done.

Example:
Here's a reusable procedure that you can call to toggle the settings if you
want to save the typing in every procedure you need this in:

Public Sub EnableFastCode(Optional SetFast As Boolean = True)
With Application
If SetFast Then
.ScreenUpdating = False
.Calculation = xlCalculationManual
Else
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.Calculate '//update changes
End If
End With
End Sub

To use it in a procedu
'some code...

'Turn off Calculation/ScreenUpdating
EnableFastCode
'do stuff you want to hide screen activity for...
'changes to cells or UI...
'open/close workbooks...
'process lots of data...
'Turn on Calculation/ScreenUpdating, & recalculate
EnableFastCode False

'finish up...

You might want to read up on the various contexts that the Calculate method
can be used so you fully understand what global effect it has, as well as how
to use it on specific worksheets or cells.

HTH
Garry