Hi Dave
I went through and added more debug.print Screenupdating statements. I also
added Worksheet Calculates to those sheets that did not have one. They are
just debug.print Screenupdating statements. I wanted to see if the program
went there. Any sheet that would normally update based on other sheets
executed their worksheet calculates. The pages which are more independent
did not execute.
I have debug.print “Start ProcedureName†all through my code. I thought
perhaps something was running I wasn’t aware of. Apparently not, the
worksheet calculates are the only things that ran. No other procedure’s
debug.print statements executed.
I tried to step through the Workbook Open code to see where it went but the
buck stopped there.
I removed the screenupdating true statement at the end of the Workbook Open
procedure to see what would happen. All of the worksheet calculates run
after the Workbook Open procedure is finished.
Screenupdating is false at the close of the workbook open procedure but is
true again right out of the gate at the beginning of the first worksheet
calculate that runs. Whatever is triggering the worksheet calculates is also
changing the screenupdating to true.
I may be confused on this but how would I change the update status back to
true once the worksheet calculates finish if I leave it false at the end of
the workbook open procedure? Assuming I got this fixed, of course. Is my
understanding correct that they should not be running at all on workbook open?
I am unable to read all of your posts again. Right now some of them are
blank for some reason. I remember you had something about freezing the
window but you didn’t recommend it. Consequently, I’m afraid to try it. But
would freezing the window show me what is triggering the worksheet calculates?
I came up with a work-a-round. I disabled events, paused, then enabled
events. I’m not as experienced as you. Do you see a problem with it? I
don’t want it to bite me in the behind later. I am also concerned that
whatever is causing this could be slowing down the overall efficiency of the
workbook or be causing ‘glitches.’ So far this works like a charm but I’m
not sure it will be as effective on the larger workbooks with more tenants or
that the initial problem isn’t causing problems elsewhere.
If you have no more suggestions for me do you have any recommendations as to
where else to look for help?
Private Sub Workbook_Open()
Dim wkSheet As Worksheet
Application.ScreenUpdating = False
Debug.Print "wkbook open Screenupdating " & _
Application.ScreenUpdating
For Each wkSheet In Worksheets
Debug.Print wkSheet.Name & " start protect ScreenUpdating " & _
Application.ScreenUpdating
wkSheet.Protect Password:="abcdefg", _
userinterfaceonly:=True, contents:=True, _
AllowFormattingCells:=True
Debug.Print wkSheet.Name & " end protect Screenupdating " & _
Application.ScreenUpdating
Next wkSheet
Debug.Print "ProtectWkbook Procedure Start Screenupdating " & _
Application.ScreenUpdating
Call AddSheets.ProtectWkbook
Debug.Print "ProtectWkbook Procedure end Screenupdating " & _
Application.ScreenUpdating
MainPagepg.Activate
Debug.Print "MainPage Activate Screenupdating " & _
Application.ScreenUpdating
Application.ScreenUpdating = True
Debug.Print "Workbook Open complete Screenupdating? " & _
Application.ScreenUpdating
Application.EnableEvents = False
Debug.Print "workbook Open 1 EnableEvents " & _
Application.EnableEvents
Application.Wait Second(Now()) + 10
Application.EnableEvents = True
Debug.Print "workbook Open 2 EnableEvents " & _
Application.EnableEvents
End Sub
--
Thanks for your help.
Karen53
Dave Peterson said:
It doesn't look like you actually printed the .screenupdating status on all your
debug.print lines.
But I'm out of suggestions if you find that they're all false (before and after
each procedure).
Karen53 wrote:
Hi Dave,
I went through again and double checked I had all of the screenupdating
statements removed. They are gone.
I added debug.prints in the Workbook Open proceudure and again before each
next Wksheet. They are all false for screenupdating.
I also added debug.prints in each of the worksheet calculates. For some
reason they run not once, but twice once the workbook open is finished. I
thought maybe it was the mainpagepg.activate so I removed it. I got the same
results.
wkbook open False
Master Page False
GL Line Items False
Gross Up False
Sum by Line Item False
First False
Jack Sparrow False
Jessica Rabbit False
Lois Lane False
James Bond False
Sherlock Holmes False
Dread Pirate Roberts False
Indiana Jones False
Austin Powers False
Last False
Maintenance False
CAM Master False
Tables False
ProtectWkbook Procedure False
MainPage Activate
Calculate Austin Powers
Calculate Dread Pirate Roberts
Calculate Indiana Jones
Calculate Jack Sparrow
Calculate James Bond
Calculate Jessica Rabbit
Calculate Lois Lane
Calculate Sherlock Holmes
Calculate Austin Powers
Calculate Dread Pirate Roberts
Calculate Indiana Jones
Calculate Jack Sparrow
Calculate James Bond
Calculate Jessica Rabbit
Calculate Lois Lane
Calculate Sherlock Holmes
There is no more code in the workbook open procedure. None of the values
have changed since the workbook was closed. Why are the calculates running,
twice yet?
Do you have any more debuging suggestions?
--
Thanks for your help.
Karen53
:
First, I'd double check at all those UDFs again for screenupdating lines. I'm
not sure why you'd have that in a UDF anyway.
I still don't see anything in the code you posted that would toggle that
screenupdating setting.
There are things built into excel that will toggle the .screenupdating setting
to off. If I remember correctly, if you call some (not sure which) functions
from the analysis toolpak, then that setting could be changed to true.
If that's the case, you have a couple of options.
1. Find those lines (pepper your code with lines like:
debug.print "some step # here " & application.screenupdating
Then run the macro and see where True shows up.
2. The other option is to use an API call that actually freezes your display.
This can be dangerous. If your code breaks before you can turn the display back
on, you'll be rebooting your pc--and losing any work that hasn't been saved.
At the top of the module:
Declare Function LockWindowUpdate Lib _
"user32" (ByVal hwndLock As Long) As Long
Declare Function FindWindowA Lib _
"user32" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
In your code:
Sub whatever()
'do stuff
'freeze the screen
hWnd = FindWindowA("XLMAIN", Application.Caption)
LockWindowUpdate hWnd
'do more stuff
'unfreeze the screen
LockWindowUpdate 0
'do more stuff
End sub
Remember to save your work often (in all open applications!) if you use this.
You may be rebooting more than you want. (I wouldn't use this--but I've said
this before.)
Karen53 wrote:
Hi Dave,
No, this was made on the same version of excel, so it's not a previous
version.
Here is the AddSheets.ProtectWkbook procedure...
Sub ProtectWkbook()
Dim IsProtected As Boolean
IsProtected = False
If ActiveWorkbook.ProtectStructure Then IsProtected = True
If IsProtected = False Then
ActiveWorkbook.Protect Password:=([MyPassword]), Structure:=True,
Windows:=False
End If
End Sub
Just on the chance, I removed any screenupdating code on the
worksheet_calculates but the workbook still flickers on opening.
--
Thanks for your help.
Karen53
:
Any chance that AddSheets.ProtectWkbook turns the screenupdating off? I'd look
there first.
There are some things (pretty unusual, though) that turn screenupdating on. But
I don't see anything in your posted code that would do that.
And xl will recalc if you open a workbook that was saved in a previous version.
Could that be the problem?
Karen53 wrote:
Hi,
I have a workbook with a Workbook_Open procedure in the ThisWorkbook module.
Here is the code..
Private Sub Workbook_Open()
Dim wkSheet As Worksheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each wkSheet In Worksheets
wkSheet.Protect Password:="abcdefg", _
userinterfaceonly:=True, contents:=True, _
AllowFormattingCells:=True
Next wkSheet
