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?
--
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?
--
Thanks for your help.
Karen53

--

Dave Peterson

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]), Structu=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" wrote:

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?
--
Thanks for your help.
Karen53

--

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

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]), Structu=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" wrote:

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?
--
Thanks for your help.
Karen53

--

Dave Peterson


--

Dave Peterson

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" wrote:

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]), Structu=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" wrote:

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?
--
Thanks for your help.
Karen53

--

Dave Peterson


--

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

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

"Dave Peterson" wrote:

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]), Structu=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" wrote:

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?
--
Thanks for your help.
Karen53

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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" wrote:

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?
--
Thanks for your help.
Karen53

--

Dave Peterson

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" wrote:

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?
--
Thanks for your help.
Karen53

--

Dave Peterson