ScreenUpdating = False appears to not work 1

[shawnmandel]

I have a simple subroutine that loops through a set of worksheets.

I have the statement Application.ScreenUpdating = False, but it does not appear to be working, as I can still see the blips as it cycles through the workbook.
Also - when I step through the code, after processing that specific line, Application.ScreenUpdating indicates that it is set to True.

I'm using Excel 2002 on Windows 2000 Pro SP-4.

Any ideas?

Thanks,
Shawn

 

[PHV]

When you go to VBE, ScreenUpdating becomes automagically True.
Do a search on your whole project for ScreenUpdating to see if somewhere it is set to True.
Or continue to step your code to see what happens.

Hope This Help
PH.

 

[shawnmandel]

Hi PH,

I've done a trace and nowhere in my code does it reset ScreenUpdating back to TRUE. In fact, I commented out every single instance of ScreenUpdating and the only one I kept is the one for a single subroutine. I get the same problem.

What do you think it is?

Thanks very much for your response.

Shawn

 

[GlennUK]

As PHV said, you can't use stepping through the code, as the VBE sets screenupdating to TRUE.

You'll have to embed
MsgBox Application.ScreenUpdating
statements within your code to track down which command or statement is causing this.

You should see messages saying False until the code gets past the statement that's causing the trouble, then it will say True.

Cheers, Glenn.

 

[shawnmandel]

This is what I am isolating:

Sub Merge_Data_Button()
Application.ScreenUpdating = False
My_Code
Application.ScreenUpdating = True
End Sub


My_Code has no ScreenUpdating parameters.

When I execute my Merge_Data_Button subroutine, my screen goes crazy on me. My ScreenUpdating does not seem to want to turn off...

Am I doing anything wrong?

Regards,
Shawn

 

[SkipVought]

Shawn,

Please post your My_Code code.

faq707-4105 How Can I Make My Code Run Faster?



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[shawnmandel]

My_Code consists of several subroutines and consists of more than 1000 lines of code. It would be quite daunting to try to post. Can you let me know what to look for?

Regards,
Shawn

 

[SkipVought]

Can't say for sure.

Where is your button? On a Form or on a Sheet?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[shawnmandel]

Hi Skip,

The button is on a sheet. I assign my button macro to it. It is a control toolbox button.

Regards,
Shawn

 

[shawnmandel]

Here is an example that works perfectly. I get no blips:

Sub UnHide_Columns_Button()
Application.ScreenUpdating = False
UnHide_Columns
Application.Goto Reference:="R1C1"
Application.ScreenUpdating = True
End Sub

Sub UnHide_Columns()
UnProtect_Sheets
Range("DataRange&quot.Columns.Select
Selection.EntireColumn.Hidden = False
End Sub


I'm wondering why this one works.

 

[SkipVought]

THis would work better...

Sub UnHide_Columns_Button()
    Application.ScreenUpdating = False
    UnHide_Columns
    [A1].Select
    Application.ScreenUpdating = True
End Sub

Sub UnHide_Columns()
    UnProtect_Sheets
    Range("DataRange").Columns.EntireColumn.Hidden = False
End Sub

What does UnProtect_Sheets do?

On occasion, a Button on a sheet can be FIRED AGAIN by certain events that occurr on that sheet or even other sheets.

I would ALSO. in addition to ScreenUpdating, disable events...

Sub Merge_Data_Button()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    My_Code
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[shawnmandel]

Skip,

Here is Unprotect_Sheets:

Sub UnProtect_Sheets()
UserInput.Unprotect Password
Sheets(file_name).Unprotect Password
A_Formulas.Unprotect Password
End Sub

I will attempt your suggestions.

Thanks,
Shawn

 

[shawnmandel]

Hey Skip,

All of your suggestions worked for me! I'm unsure how this remedied the situation but the ScreenUpdating now works like a charm.

Strangely enough, before upgrading to Office XP, the ScreenUpdating worked fine in Excel 2000, but in Excel 2002, the ScreenUpdating stopped working properly for me.

After adding Application.EnableEvents and making thise changes to my Unhide_Button subroutines, things now work nicely.

Thanks very much for you help,
Shawn