Workbook Open


K

Karen53

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

Call AddSheets.ProtectWkbook

MainPagepg.Activate

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

I get screen flickering each time the workbook is opened. I added the
application.screenupdating and application.calculation commands to try and
stop the flickering. The flickering still occcurs.

Do any worksheet_calculate procedures run everytime the workbook is opened
or only when something is changed? I am trying to figure out where the
flickering is coming from. Does anyone have any suggestions?
 
Ad

Advertisements

D

Dave Peterson

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?
 
K

Karen53

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


Dave Peterson said:
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?
 
K

Karen53

Hi Dave,

Yes, it is running each Worksheet_Change everytime the workbook is opened.
Is there a way I can stop this?
--
Thanks for your help.
Karen53


Dave Peterson said:
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?
 
K

Karen53

Hi,

I added application enableevents to the Workbook Open procedure and removed
the application.enableevents = true statement from all of the
worksheet_calculate procedures and the flickering stopped. Of course now the
individual worksheets are not working correctly. How can I resolve this?
The workbook was created here at work but I did make modifications at home.
We are both V 2003 SP2. This shouldn't cause this to happen, would it?
--
Thanks for your help.
Karen53


Dave Peterson said:
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?
 
D

Dave Peterson

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.)
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.
 
Ad

Advertisements

K

Karen53

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


Dave Peterson said:
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.)
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

Dave Peterson said:
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

Call AddSheets.ProtectWkbook

MainPagepg.Activate

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

I get screen flickering each time the workbook is opened. I added the
application.screenupdating and application.calculation commands to try and
stop the flickering. The flickering still occcurs.

Do any worksheet_calculate procedures run everytime the workbook is opened
or only when something is changed? I am trying to figure out where the
flickering is coming from. Does anyone have any suggestions?
 
D

Dave Peterson

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).
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

Dave Peterson said:
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.)
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

Call AddSheets.ProtectWkbook

MainPagepg.Activate

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

I get screen flickering each time the workbook is opened. I added the
application.screenupdating and application.calculation commands to try and
stop the flickering. The flickering still occcurs.

Do any worksheet_calculate procedures run everytime the workbook is opened
or only when something is changed? I am trying to figure out where the
flickering is coming from. Does anyone have any suggestions?
 
K

Karen53

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).
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

Dave Peterson said:
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

Call AddSheets.ProtectWkbook

MainPagepg.Activate

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

I get screen flickering each time the workbook is opened. I added the
application.screenupdating and application.calculation commands to try and
stop the flickering. The flickering still occcurs.

Do any worksheet_calculate procedures run everytime the workbook is opened
or only when something is changed? I am trying to figure out where the
flickering is coming from. Does anyone have any suggestions?
 
D

Dave Peterson

First, you can use google to find old posts. Maybe you can read the old posts
from there.

http://groups.google.com/advanced_group_search

Search in the *excel* newsgroup and give it enough info to limit the search to
your thread.

Second, you had this line in the code you posted.
Application.ScreenUpdating = True

Did you really clean up all those .screenupdating = true lines? And did you
clean up all the screenupdating lines in each of the called modules?

Your posted code just shows what happens when it gets back to the calling
procedure.
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).
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

Call AddSheets.ProtectWkbook

MainPagepg.Activate

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

I get screen flickering each time the workbook is opened. I added the
application.screenupdating and application.calculation commands to try and
stop the flickering. The flickering still occcurs.

Do any worksheet_calculate procedures run everytime the workbook is opened
or only when something is changed? I am trying to figure out where the
flickering is coming from. Does anyone have any suggestions?
 
K

Karen53

Hi Dave,

Thank you! Thank you! Thank you!

LOL! I have to admit I am still confused as to why but I am hoping you will
explain it to me. I had this lengthly reply all set to post back to you but
something about your last post made me wonder. So I cleared the
ScreenUpdating false and ScreenUpdating True from my Workbook Open procedure
and it opens beautifully! The worksheet calculates still fire twice but I'm
not getting all the flicker on opening. I guess you can lead a horse to
water, and hopefully, eventually, they'll drink! Neigh! Thanks for sticking
with this horse!

So my questions are:
Why did this cause flicker or is it one of those things you just have to
accept it is so?

Will the worksheet calculates firing twice each time the workbook opens
cause the workbook opening to slow down dramatically? Some of the workbooks
have a lot of tenant sheets. Do I need to worry about it?

If I do need to worry about it, I am thinking somehow two values are
perceived as being changed as the worksheet calculates run twice. Is there a
way to capture which cell values changed? Perhaps that would lead me to what
is causing the calcuates to fire.

I have to say Dave, you have been great throughout this project. I have
learned so much from you! Thank you! Thank you! Thank you!

Thank you for the reference link as well. I think I'll go exploring.

--
Thanks for your help.
Karen53


Dave Peterson said:
First, you can use google to find old posts. Maybe you can read the old posts
from there.

http://groups.google.com/advanced_group_search

Search in the *excel* newsgroup and give it enough info to limit the search to
your thread.

Second, you had this line in the code you posted.
Application.ScreenUpdating = True

Did you really clean up all those .screenupdating = true lines? And did you
clean up all the screenupdating lines in each of the called modules?

Your posted code just shows what happens when it gets back to the calling
procedure.
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
 
Ad

Advertisements

D

Dave Peterson

Karen,

I have no idea why changing your workbook_Open event would fix the problem--but
I'm glad it did (for your sake).

So I don't have any guess why your change would cause the flicker to stop.

There's nothing built into any of the events that will tell you what cells are
changed during the calculation. Maybe you could dump all the values to another
worksheet and then compare those values after the calculation.

Charles Williams has a site that discusses lots of calculation tips and secrets:
http://www.decisionmodels.com


As much as I'd like to take credit for you finding the solution, I don't think I
can. I really, really don't know why the last thing you change worked!
Hi Dave,

Thank you! Thank you! Thank you!

LOL! I have to admit I am still confused as to why but I am hoping you will
explain it to me. I had this lengthly reply all set to post back to you but
something about your last post made me wonder. So I cleared the
ScreenUpdating false and ScreenUpdating True from my Workbook Open procedure
and it opens beautifully! The worksheet calculates still fire twice but I'm
not getting all the flicker on opening. I guess you can lead a horse to
water, and hopefully, eventually, they'll drink! Neigh! Thanks for sticking
with this horse!

So my questions are:
Why did this cause flicker or is it one of those things you just have to
accept it is so?

Will the worksheet calculates firing twice each time the workbook opens
cause the workbook opening to slow down dramatically? Some of the workbooks
have a lot of tenant sheets. Do I need to worry about it?

If I do need to worry about it, I am thinking somehow two values are
perceived as being changed as the worksheet calculates run twice. Is there a
way to capture which cell values changed? Perhaps that would lead me to what
is causing the calcuates to fire.

I have to say Dave, you have been great throughout this project. I have
learned so much from you! Thank you! Thank you! Thank you!

Thank you for the reference link as well. I think I'll go exploring.
<<snipped>>
 
K

Karen53

Hi Dave,

I've got the problem of the worksheet calculate firing each time the
workbook opens narrowed down to one sheet. That link you sent me about
calculation is great. I suspect conditional formating is my culprit. I
thought I had removed them all but I need to check if I missed any. Is there
a way to locate any conditional formating on a worksheet?
 
K

Karen53

Hi Gordon,

Thank you for your reply.

I tried F5, that brings up my GoTo navigation window with my named ranges
listed. It will not accept 'Special' saying it's invalid. Is there
something I am missing?
 
Ad

Advertisements

K

Karen53

Hi Gord,

Never mind. The sheet was protected. The result is no conditional
formating. Back to my search...
 
K

Karen53

Well, I just don't know what else to try. I can't find what on this page is
triggering worksheet Calculate to run twice on workbook open. Nothing
changes. I've checked for volatiles. I do use Indirect, but a lot more than
twice. Wouldn't it run once for each time I use it? I tried removing them
but it didn't make a difference. It wouldn't be so bad if it only ran once,
but twice?

There are no cells containing conditional formating, no autofilter.

I do use index but I'm in 2003 so it should no longer be volatile according
to www.decisionmodels.com. I use it many times. Again, wouldn't it run once
for each time I use it?
 
Ad

Advertisements

D

Dave Peterson

I don't know. I exhausted my calculation expertise when I posted that link to
Charles Williams's web site.

Sorry.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top