Flashing through sheets in Excel 2

[Stretchwickster]

I have a program which adds, deletes and hides various sheets and charts as it process the data on them and does various operations. During processing, the sheets flash by as they are updated.

Is there a way to do all the processing then update the sheets in one go at the end, so that there is not this flashing effect as the program is executed? Clive

 

[SkipVought]

Hi,

Application.ScreenUpdating = FALSE
[\code] Skip,
SkipAndMary1017@mindspring.com

 

[SkipVought]

Stretchwickster,

Punched the SUBMIT too soon...

Add this Property of the Application Object before you begin activating sheets.

If you need to show what is happening with your code, then you can set the property to TRUE.

Hope this helps Skip,
SkipAndMary1017@mindspring.com

 

[TomCarnahan]

Stretchwickster,

Just another little freebee that I have found useful. Sometimes I need to see the sheets "flying by" as I step through the code, but when I am ready to put the procedure in operation, I want to make everything run behind the scenes. I have been using "Compiler Constants" (See Help in the VBE editor) in all my procedures. When I am testing, I set the compiler constant to TRUE; when I am done, I set it to FALSE. The following is an example of a module and one dummy procedure at the top of the procedure:


'-------------------------------------
' In the module Declartion section
'-------------------------------------
#Const blnTesting = True

Option Explicit
______________________________________

Public Sub MyRoutine()

On Err GoTo Err_MyRoutine

With Application
#if blnTesting then
.ScreenUpDating = True
#else
.ScreenUpDating = False
#end if

'Dim statements go here

' <working code goes here.

Exit_MyRoutine:
On Error Resume Next 'Stops recursive errors.

'Screen back on as appropriate here.
Application.ScreenUpdating = True
'Set Object variables equal to nothing here.
Exit Sub

Err_MyRoutine:
MsgBox Err.Description, vbCritialc, Err.Number

Application.ScreenUpDating = True
#If blnTesting then
stop
resume next
#Else
resume Exit_MyRoutine
#End If

End Sub
_______________________________________________

This also shows how you can use the conditional constants to help troubleshooting. While testing, rather than dumping out of the routine, it gives you the error description, number, then stops. You can press F8 to single step back to the line after the error. Since the code is still being executed, you can put the cursor over most variables and check their values.

When you are done testing, go to the top of the module, change the statement to: #Const blnTesting = False
After that, be sure to COMPILE. The compiler will substitute the code that will stop the &quot;flashing&quot; windows, and bypass the stuff you only do during error checking.

Note: keeping in good coding form, all exits from your routine, whether by error or normal, should exit via the &quot;Exit_MyRoutine&quot; label. It makes trouble shooting easier and is a better form to use.

I hope this was constructive!

--- Tom
--- Tom