Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 142
  • Last Modified:

Odd ScreenUpdating=False Problem

Hi Experts, I'm in excel 2007 experiencing a weird issue.
Please open the attached workbook.

Please try clicking any of the navigation buttons. You'll see that as you navigate between sheets a box will appear highlighting the used range before switching worksheets. That is because of the following code to set the zoom on each sheet:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'set zoom for active sheet to columns with data
 ActiveSheet.UsedRange.Resize(ActiveSheet.UsedRange.Columns.Count).Select
 ActiveWindow.Zoom = True: ActiveWindow.VisibleRange(1, 1).Select
End Sub

Open in new window

Now what's odd is that on Workbook_Open I have set ScreenUpdating equal to false see the below code:
Private Sub Workbook_Open()
Application.ScreenUpdating = False
Call FormatWorkbook
'activate StandardMode
Sheets("StandardMode").Activate
End Sub

Open in new window

I've discovered that if I add another Application.ScreenUpdating = False statement to the zoom code it will work....... but why would I have to set it to false twice? Is it being set to true after Workbook Open somehow?
EESafe-UI-Input-09.24.xlsm
0
-Polak
Asked:
-Polak
  • 4
  • 2
  • 2
2 Solutions
 
Martin LissRetired ProgrammerCommented:
It's a quirk but ScreenUpdating always sets itself to True at the and of any routine.
0
 
Martin LissRetired ProgrammerCommented:
And you can prove it by creating these two macros. Run x and then run y.
Sub x()
Application.ScreenUpdating = False
End Sub

Sub y()
MsgBox Application.ScreenUpdating
End Sub

Open in new window

0
 
Rob HensonIT & Database AssistantCommented:
Does that mean you don't have to consciously put:

Application.ScreenUpdating = True

At the end of a routine?

Thanks
Rob H
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Martin LissRetired ProgrammerCommented:
That's correct, but I do it anyhow for clarity.
0
 
-PolakAuthor Commented:
Well alrighty then!
0
 
Martin LissRetired ProgrammerCommented:
I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
 
Rob HensonIT & Database AssistantCommented:
I guess I am thinking of times when I have included a Stop command in the script so have then had to manually reset the Screen Update to True
0
 
-PolakAuthor Commented:
Hey guys, just a heads up on this, while it does seem to set itself back to true in every other circumstance; if you don't have it set to true at the end of a subroutine and then insert another worksheet it will appear as if excel has frozen. (this was excel 2007)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now