Workbook Open
 

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

Workbook Open
 

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

Workbook Open
 

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

Workbook Open
 

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

Workbook Open
 

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

Workbook Open
 

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

Workbook Open
 

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

Workbook Open
 

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

Workbook Open
 

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 wasnt aware of. Apparently not, the
worksheet calculates are the only things that ran. No other procedures
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 didnt recommend it. Consequently, Im 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. Im not as experienced as you. Do you see a problem with it? I
dont 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 Im
not sure it will be as effective on the larger workbooks with more tenants or
that the initial problem isnt 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" wrote:

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

Workbook Open
 

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.

Karen53 wrote:

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 wasnt aware of. Apparently not, the
worksheet calculates are the only things that ran. No other procedures
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 didnt recommend it. Consequently, Im 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. Im not as experienced as you. Do you see a problem with it? I
dont 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 Im
not sure it will be as effective on the larger workbooks with more tenants or
that the initial problem isnt 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" wrote:

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


--

Dave Peterson

Workbook Open
 

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

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.

Karen53 wrote:

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

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

Workbook Open
 

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!

Karen53 wrote:

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

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.

<<snipped

Workbook Open
 

Hi Dave,

Maybe so, but you've still given me a lot of help throughout this project!

--
Thanks for your help.
Karen53


"Dave Peterson" wrote:

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!

Karen53 wrote:

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

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.

<<snipped

Workbook Open
 

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

Sorry.

Karen53 wrote:

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?

--
Thanks for your help.
Karen53

"Karen53" wrote:

Hi Gord,

Never mind. The sheet was protected. The result is no conditional
formating. Back to my search...
--
Thanks for your help.
Karen53


"Gord Dibben" wrote:

F5SpecialConditional Formats


Gord Dibben MS Excel MVP

On Tue, 19 Feb 2008 10:41:01 -0800, Karen53
wrote:

Is there
a way to locate any conditional formating on a worksheet?



--

Dave Peterson

Workbook Open
 

Hi Dave,

I discovered this while trying to trouble shoot something else. Earlier in
this post we were talking about volatile code. I've found "Replace" in the
code changes the screenupdating status to True.

Sub AddFormulaLinks(ShName, NextRow)

' add the formulas to link Main Page to the new sheet

Debug.Print "Starting AddFormulaLinks " & Application.ScreenUpdating

Dim iCtr As Long
Dim Cell As Range


Sheets(ShName).Activate

With Sheets(ShName)

'Unit Status Current? from Main Page =IF(ISBLANK('Master
Page'!$B$16),"", 'Master Page'!$B$14)
With .Range("B1")
.HorizontalAlignment = xlCenter
Debug.Print "AddFormulaLinks center " &
Application.ScreenUpdating

.WrapText = False
Debug.Print "AddFormulaLinks WrapText " &
Application.ScreenUpdating

.NumberFormat = "General"
Debug.Print "AddFormulaLinks No Format General " &
Application.ScreenUpdating

.FormulaR1C1 = "=If(ISBLANK('" & Replace(MainPagepg.Name, "'",
"''") & "'!R" _
& NextRow & "C2) ,"""", '" &
Replace(MainPagepg.Name, "'", "''") & "'!R" & NextRow & "C2)"
Debug.Print "AddFormulaLinks Replace " &
Application.ScreenUpdating

End With

Immediate window:
AddSheet call AddFormulaLinks False
Starting AddFormulaLinks False
AddFormulaLinks center False
AddFormulaLinks WrapText False
AddFormulaLinks No Format General False
AddFormulaLinks Replace True




--
Thanks for your help.
Karen53


"Dave Peterson" wrote:

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

Sorry.

Karen53 wrote:

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?

--
Thanks for your help.
Karen53

"Karen53" wrote:

Hi Gord,

Never mind. The sheet was protected. The result is no conditional
formating. Back to my search...
--
Thanks for your help.
Karen53


"Gord Dibben" wrote:

F5SpecialConditional Formats


Gord Dibben MS Excel MVP

On Tue, 19 Feb 2008 10:41:01 -0800, Karen53
wrote:

Is there
a way to locate any conditional formating on a worksheet?



--

Dave Peterson

Workbook Open
 

I'm not sure if it's really the =replace() worksheet function.

I tried this simple test:

Option Explicit
Sub testme()
Dim myCell As Range

Application.ScreenUpdating = False
Debug.Print "After step 001: " & Application.ScreenUpdating

Set myCell = ActiveSheet.Range("A1")
Debug.Print "After step 002: " & Application.ScreenUpdating

myCell.Formula = "=REPLACE(B1,1,3,""xx"")"
Debug.Print "After step 003: " & Application.ScreenUpdating

Application.ScreenUpdating = True
Debug.Print "After step 004: " & Application.ScreenUpdating

End Sub

And got:

After step 001: False
After step 002: False
After step 003: False
After step 004: True

But my guess is that you have something from the analysis toolpak on that sheet
(or in that workbook) that recalcs after you change the formula. And that call
to the analyis toolpak turns on .screenupdating.

Your next challenge <vbg is to search for that offending "something".


Karen53 wrote:

Hi Dave,

I discovered this while trying to trouble shoot something else. Earlier in
this post we were talking about volatile code. I've found "Replace" in the
code changes the screenupdating status to True.

Sub AddFormulaLinks(ShName, NextRow)

' add the formulas to link Main Page to the new sheet

Debug.Print "Starting AddFormulaLinks " & Application.ScreenUpdating

Dim iCtr As Long
Dim Cell As Range


Sheets(ShName).Activate

With Sheets(ShName)

'Unit Status Current? from Main Page =IF(ISBLANK('Master
Page'!$B$16),"", 'Master Page'!$B$14)
With .Range("B1")
.HorizontalAlignment = xlCenter
Debug.Print "AddFormulaLinks center " &
Application.ScreenUpdating

.WrapText = False
Debug.Print "AddFormulaLinks WrapText " &
Application.ScreenUpdating

.NumberFormat = "General"
Debug.Print "AddFormulaLinks No Format General " &
Application.ScreenUpdating

.FormulaR1C1 = "=If(ISBLANK('" & Replace(MainPagepg.Name, "'",
"''") & "'!R" _
& NextRow & "C2) ,"""", '" &
Replace(MainPagepg.Name, "'", "''") & "'!R" & NextRow & "C2)"
Debug.Print "AddFormulaLinks Replace " &
Application.ScreenUpdating

End With

Immediate window:
AddSheet call AddFormulaLinks False
Starting AddFormulaLinks False
AddFormulaLinks center False
AddFormulaLinks WrapText False
AddFormulaLinks No Format General False
AddFormulaLinks Replace True

--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

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

Sorry.

Karen53 wrote:

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?

--
Thanks for your help.
Karen53

"Karen53" wrote:

Hi Gord,

Never mind. The sheet was protected. The result is no conditional
formating. Back to my search...
--
Thanks for your help.
Karen53


"Gord Dibben" wrote:

F5SpecialConditional Formats


Gord Dibben MS Excel MVP

On Tue, 19 Feb 2008 10:41:01 -0800, Karen53
wrote:

Is there
a way to locate any conditional formating on a worksheet?



--

Dave Peterson


--

Dave Peterson

Workbook Open
 

Hi Dave,

Let me see if I understand correctly. It's not the 'replace' command itself
but the fact that 'replace' changes the formula causing this toolpak entity
to recalc?

I know nothing about the analysis toolpack. Is there something online that
lists what is in it so I know what I am looking for? Is it like a group of
commands, something I might have called not knowing it's toolpack? I tried a
help search in VBA but the toolpack does not come up.
--
Thanks for your help.
Karen53


"Dave Peterson" wrote:

I'm not sure if it's really the =replace() worksheet function.

I tried this simple test:

Option Explicit
Sub testme()
Dim myCell As Range

Application.ScreenUpdating = False
Debug.Print "After step 001: " & Application.ScreenUpdating

Set myCell = ActiveSheet.Range("A1")
Debug.Print "After step 002: " & Application.ScreenUpdating

myCell.Formula = "=REPLACE(B1,1,3,""xx"")"
Debug.Print "After step 003: " & Application.ScreenUpdating

Application.ScreenUpdating = True
Debug.Print "After step 004: " & Application.ScreenUpdating

End Sub

And got:

After step 001: False
After step 002: False
After step 003: False
After step 004: True

But my guess is that you have something from the analysis toolpak on that sheet
(or in that workbook) that recalcs after you change the formula. And that call
to the analyis toolpak turns on .screenupdating.

Your next challenge <vbg is to search for that offending "something".


Karen53 wrote:

Hi Dave,

I discovered this while trying to trouble shoot something else. Earlier in
this post we were talking about volatile code. I've found "Replace" in the
code changes the screenupdating status to True.

Sub AddFormulaLinks(ShName, NextRow)

' add the formulas to link Main Page to the new sheet

Debug.Print "Starting AddFormulaLinks " & Application.ScreenUpdating

Dim iCtr As Long
Dim Cell As Range


Sheets(ShName).Activate

With Sheets(ShName)

'Unit Status Current? from Main Page =IF(ISBLANK('Master
Page'!$B$16),"", 'Master Page'!$B$14)
With .Range("B1")
.HorizontalAlignment = xlCenter
Debug.Print "AddFormulaLinks center " &
Application.ScreenUpdating

.WrapText = False
Debug.Print "AddFormulaLinks WrapText " &
Application.ScreenUpdating

.NumberFormat = "General"
Debug.Print "AddFormulaLinks No Format General " &
Application.ScreenUpdating

.FormulaR1C1 = "=If(ISBLANK('" & Replace(MainPagepg.Name, "'",
"''") & "'!R" _
& NextRow & "C2) ,"""", '" &
Replace(MainPagepg.Name, "'", "''") & "'!R" & NextRow & "C2)"
Debug.Print "AddFormulaLinks Replace " &
Application.ScreenUpdating

End With

Immediate window:
AddSheet call AddFormulaLinks False
Starting AddFormulaLinks False
AddFormulaLinks center False
AddFormulaLinks WrapText False
AddFormulaLinks No Format General False
AddFormulaLinks Replace True

--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

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

Sorry.

Karen53 wrote:

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?

--
Thanks for your help.
Karen53

"Karen53" wrote:

Hi Gord,

Never mind. The sheet was protected. The result is no conditional
formating. Back to my search...
--
Thanks for your help.
Karen53


"Gord Dibben" wrote:

F5SpecialConditional Formats


Gord Dibben MS Excel MVP

On Tue, 19 Feb 2008 10:41:01 -0800, Karen53
wrote:

Is there
a way to locate any conditional formating on a worksheet?



--

Dave Peterson


--

Dave Peterson

Workbook Open
 

This is my guess...

It's not the =replace() function itself that's causing what you see. You're
changing the formula in a cell. That means that every formula that depends on
that cell is going to recalculate.

And if there's a function from the analysis toolpak addin in your workbook that
is in a cell that's recalculating, then that could be the problem.

If you look at Tools|Addins, you'll see if you have the analysis toolpak addin
checked. If that's unchecked, then the formulas that rely on those functions
should break. (Maybe close excel and reopen, then reopen your workbook to see
it.)

There are a lot of functions in that analysis toolpak.

Peter Noneley's Excel Function Dictionary has lots of information about excel
functions:
http://www.xlfdic.com

Norman Harker has his version at Debra Dalgleish's site:
http://www.contextures.com/functions.html

Norman's looks like a more complete list.

Karen53 wrote:

Hi Dave,

Let me see if I understand correctly. It's not the 'replace' command itself
but the fact that 'replace' changes the formula causing this toolpak entity
to recalc?

I know nothing about the analysis toolpack. Is there something online that
lists what is in it so I know what I am looking for? Is it like a group of
commands, something I might have called not knowing it's toolpack? I tried a
help search in VBA but the toolpack does not come up.
--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

I'm not sure if it's really the =replace() worksheet function.

I tried this simple test:

Option Explicit
Sub testme()
Dim myCell As Range

Application.ScreenUpdating = False
Debug.Print "After step 001: " & Application.ScreenUpdating

Set myCell = ActiveSheet.Range("A1")
Debug.Print "After step 002: " & Application.ScreenUpdating

myCell.Formula = "=REPLACE(B1,1,3,""xx"")"
Debug.Print "After step 003: " & Application.ScreenUpdating

Application.ScreenUpdating = True
Debug.Print "After step 004: " & Application.ScreenUpdating

End Sub

And got:

After step 001: False
After step 002: False
After step 003: False
After step 004: True

But my guess is that you have something from the analysis toolpak on that sheet
(or in that workbook) that recalcs after you change the formula. And that call
to the analyis toolpak turns on .screenupdating.

Your next challenge <vbg is to search for that offending "something".


Karen53 wrote:

Hi Dave,

I discovered this while trying to trouble shoot something else. Earlier in
this post we were talking about volatile code. I've found "Replace" in the
code changes the screenupdating status to True.

Sub AddFormulaLinks(ShName, NextRow)

' add the formulas to link Main Page to the new sheet

Debug.Print "Starting AddFormulaLinks " & Application.ScreenUpdating

Dim iCtr As Long
Dim Cell As Range


Sheets(ShName).Activate

With Sheets(ShName)

'Unit Status Current? from Main Page =IF(ISBLANK('Master
Page'!$B$16),"", 'Master Page'!$B$14)
With .Range("B1")
.HorizontalAlignment = xlCenter
Debug.Print "AddFormulaLinks center " &
Application.ScreenUpdating

.WrapText = False
Debug.Print "AddFormulaLinks WrapText " &
Application.ScreenUpdating

.NumberFormat = "General"
Debug.Print "AddFormulaLinks No Format General " &
Application.ScreenUpdating

.FormulaR1C1 = "=If(ISBLANK('" & Replace(MainPagepg.Name, "'",
"''") & "'!R" _
& NextRow & "C2) ,"""", '" &
Replace(MainPagepg.Name, "'", "''") & "'!R" & NextRow & "C2)"
Debug.Print "AddFormulaLinks Replace " &
Application.ScreenUpdating

End With

Immediate window:
AddSheet call AddFormulaLinks False
Starting AddFormulaLinks False
AddFormulaLinks center False
AddFormulaLinks WrapText False
AddFormulaLinks No Format General False
AddFormulaLinks Replace True

--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

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

Sorry.

Karen53 wrote:

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?

--
Thanks for your help.
Karen53

"Karen53" wrote:

Hi Gord,

Never mind. The sheet was protected. The result is no conditional
formating. Back to my search...
--
Thanks for your help.
Karen53


"Gord Dibben" wrote:

F5SpecialConditional Formats


Gord Dibben MS Excel MVP

On Tue, 19 Feb 2008 10:41:01 -0800, Karen53
wrote:

Is there
a way to locate any conditional formating on a worksheet?



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Workbook Open
 

Hi Dave,

Thank you so much for those links!

I've opened and closed excel several times and opened the workbook several
times. Analysis Toolpack is not check under ToolsAddins.

Offhand I didn't recognize any of the functions they show as toolpack. I
tried one they said was toolpack =iseven(Number). It errored out with #Name.

Tomorrow I'll make a list of absolutely every funciton I use in this thing.

--
Thanks for your help.
Karen53


"Dave Peterson" wrote:

This is my guess...

It's not the =replace() function itself that's causing what you see. You're
changing the formula in a cell. That means that every formula that depends on
that cell is going to recalculate.

And if there's a function from the analysis toolpak addin in your workbook that
is in a cell that's recalculating, then that could be the problem.

If you look at Tools|Addins, you'll see if you have the analysis toolpak addin
checked. If that's unchecked, then the formulas that rely on those functions
should break. (Maybe close excel and reopen, then reopen your workbook to see
it.)

There are a lot of functions in that analysis toolpak.

Peter Noneley's Excel Function Dictionary has lots of information about excel
functions:
http://www.xlfdic.com

Norman Harker has his version at Debra Dalgleish's site:
http://www.contextures.com/functions.html

Norman's looks like a more complete list.

Karen53 wrote:

Hi Dave,

Let me see if I understand correctly. It's not the 'replace' command itself
but the fact that 'replace' changes the formula causing this toolpak entity
to recalc?

I know nothing about the analysis toolpack. Is there something online that
lists what is in it so I know what I am looking for? Is it like a group of
commands, something I might have called not knowing it's toolpack? I tried a
help search in VBA but the toolpack does not come up.
--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

I'm not sure if it's really the =replace() worksheet function.

I tried this simple test:

Option Explicit
Sub testme()
Dim myCell As Range

Application.ScreenUpdating = False
Debug.Print "After step 001: " & Application.ScreenUpdating

Set myCell = ActiveSheet.Range("A1")
Debug.Print "After step 002: " & Application.ScreenUpdating

myCell.Formula = "=REPLACE(B1,1,3,""xx"")"
Debug.Print "After step 003: " & Application.ScreenUpdating

Application.ScreenUpdating = True
Debug.Print "After step 004: " & Application.ScreenUpdating

End Sub

And got:

After step 001: False
After step 002: False
After step 003: False
After step 004: True

But my guess is that you have something from the analysis toolpak on that sheet
(or in that workbook) that recalcs after you change the formula. And that call
to the analyis toolpak turns on .screenupdating.

Your next challenge <vbg is to search for that offending "something".


Karen53 wrote:

Hi Dave,

I discovered this while trying to trouble shoot something else. Earlier in
this post we were talking about volatile code. I've found "Replace" in the
code changes the screenupdating status to True.

Sub AddFormulaLinks(ShName, NextRow)

' add the formulas to link Main Page to the new sheet

Debug.Print "Starting AddFormulaLinks " & Application.ScreenUpdating

Dim iCtr As Long
Dim Cell As Range


Sheets(ShName).Activate

With Sheets(ShName)

'Unit Status Current? from Main Page =IF(ISBLANK('Master
Page'!$B$16),"", 'Master Page'!$B$14)
With .Range("B1")
.HorizontalAlignment = xlCenter
Debug.Print "AddFormulaLinks center " &
Application.ScreenUpdating

.WrapText = False
Debug.Print "AddFormulaLinks WrapText " &
Application.ScreenUpdating

.NumberFormat = "General"
Debug.Print "AddFormulaLinks No Format General " &
Application.ScreenUpdating

.FormulaR1C1 = "=If(ISBLANK('" & Replace(MainPagepg.Name, "'",
"''") & "'!R" _
& NextRow & "C2) ,"""", '" &
Replace(MainPagepg.Name, "'", "''") & "'!R" & NextRow & "C2)"
Debug.Print "AddFormulaLinks Replace " &
Application.ScreenUpdating

End With

Immediate window:
AddSheet call AddFormulaLinks False
Starting AddFormulaLinks False
AddFormulaLinks center False
AddFormulaLinks WrapText False
AddFormulaLinks No Format General False
AddFormulaLinks Replace True

--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

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

Sorry.

Karen53 wrote:

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?

--
Thanks for your help.
Karen53

"Karen53" wrote:

Hi Gord,

Never mind. The sheet was protected. The result is no conditional
formating. Back to my search...
--
Thanks for your help.
Karen53


"Gord Dibben" wrote:

F5SpecialConditional Formats


Gord Dibben MS Excel MVP

On Tue, 19 Feb 2008 10:41:01 -0800, Karen53
wrote:

Is there
a way to locate any conditional formating on a worksheet?



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Workbook Open
 

If the analysis toolpak isn't checked, then you're not using it. So I don't
think I'd spend much time going down that road.

But I still don't have a better guess.

Karen53 wrote:

Hi Dave,

Thank you so much for those links!

I've opened and closed excel several times and opened the workbook several
times. Analysis Toolpack is not check under ToolsAddins.

Offhand I didn't recognize any of the functions they show as toolpack. I
tried one they said was toolpack =iseven(Number). It errored out with #Name.

Tomorrow I'll make a list of absolutely every funciton I use in this thing.

--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

This is my guess...

It's not the =replace() function itself that's causing what you see. You're
changing the formula in a cell. That means that every formula that depends on
that cell is going to recalculate.

And if there's a function from the analysis toolpak addin in your workbook that
is in a cell that's recalculating, then that could be the problem.

If you look at Tools|Addins, you'll see if you have the analysis toolpak addin
checked. If that's unchecked, then the formulas that rely on those functions
should break. (Maybe close excel and reopen, then reopen your workbook to see
it.)

There are a lot of functions in that analysis toolpak.

Peter Noneley's Excel Function Dictionary has lots of information about excel
functions:
http://www.xlfdic.com

Norman Harker has his version at Debra Dalgleish's site:
http://www.contextures.com/functions.html

Norman's looks like a more complete list.

Karen53 wrote:

Hi Dave,

Let me see if I understand correctly. It's not the 'replace' command itself
but the fact that 'replace' changes the formula causing this toolpak entity
to recalc?

I know nothing about the analysis toolpack. Is there something online that
lists what is in it so I know what I am looking for? Is it like a group of
commands, something I might have called not knowing it's toolpack? I tried a
help search in VBA but the toolpack does not come up.
--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

I'm not sure if it's really the =replace() worksheet function.

I tried this simple test:

Option Explicit
Sub testme()
Dim myCell As Range

Application.ScreenUpdating = False
Debug.Print "After step 001: " & Application.ScreenUpdating

Set myCell = ActiveSheet.Range("A1")
Debug.Print "After step 002: " & Application.ScreenUpdating

myCell.Formula = "=REPLACE(B1,1,3,""xx"")"
Debug.Print "After step 003: " & Application.ScreenUpdating

Application.ScreenUpdating = True
Debug.Print "After step 004: " & Application.ScreenUpdating

End Sub

And got:

After step 001: False
After step 002: False
After step 003: False
After step 004: True

But my guess is that you have something from the analysis toolpak on that sheet
(or in that workbook) that recalcs after you change the formula. And that call
to the analyis toolpak turns on .screenupdating.

Your next challenge <vbg is to search for that offending "something".


Karen53 wrote:

Hi Dave,

I discovered this while trying to trouble shoot something else. Earlier in
this post we were talking about volatile code. I've found "Replace" in the
code changes the screenupdating status to True.

Sub AddFormulaLinks(ShName, NextRow)

' add the formulas to link Main Page to the new sheet

Debug.Print "Starting AddFormulaLinks " & Application.ScreenUpdating

Dim iCtr As Long
Dim Cell As Range


Sheets(ShName).Activate

With Sheets(ShName)

'Unit Status Current? from Main Page =IF(ISBLANK('Master
Page'!$B$16),"", 'Master Page'!$B$14)
With .Range("B1")
.HorizontalAlignment = xlCenter
Debug.Print "AddFormulaLinks center " &
Application.ScreenUpdating

.WrapText = False
Debug.Print "AddFormulaLinks WrapText " &
Application.ScreenUpdating

.NumberFormat = "General"
Debug.Print "AddFormulaLinks No Format General " &
Application.ScreenUpdating

.FormulaR1C1 = "=If(ISBLANK('" & Replace(MainPagepg.Name, "'",
"''") & "'!R" _
& NextRow & "C2) ,"""", '" &
Replace(MainPagepg.Name, "'", "''") & "'!R" & NextRow & "C2)"
Debug.Print "AddFormulaLinks Replace " &
Application.ScreenUpdating

End With

Immediate window:
AddSheet call AddFormulaLinks False
Starting AddFormulaLinks False
AddFormulaLinks center False
AddFormulaLinks WrapText False
AddFormulaLinks No Format General False
AddFormulaLinks Replace True

--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

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

Sorry.

Karen53 wrote:

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?

--
Thanks for your help.
Karen53

"Karen53" wrote:

Hi Gord,

Never mind. The sheet was protected. The result is no conditional
formating. Back to my search...
--
Thanks for your help.
Karen53


"Gord Dibben" wrote:

F5SpecialConditional Formats


Gord Dibben MS Excel MVP

On Tue, 19 Feb 2008 10:41:01 -0800, Karen53
wrote:

Is there
a way to locate any conditional formating on a worksheet?



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Workbook Open
 

Hi Dave,

Do you feel this is most likely coming from VBA Code or a formiula on a
worksheet?

LOL, forget the Mad Cow! Right now I wish Scotty could beam me up!

I'm out of time. I've got to have this finished and I have absolutely no
idea how to find this problem. It is majorly impacting the workbooks. Are
there any other resources or options that I may not know about? What does
one do when you can't find the problem?

What I need is a tutor. Someone who can find the problem, help me fix it
and show me so I can learn from it.

Any suggestions?
--
Thanks for your help.
Karen53


"Dave Peterson" wrote:

If the analysis toolpak isn't checked, then you're not using it. So I don't
think I'd spend much time going down that road.

But I still don't have a better guess.

Karen53 wrote:

Hi Dave,

Thank you so much for those links!

I've opened and closed excel several times and opened the workbook several
times. Analysis Toolpack is not check under ToolsAddins.

Offhand I didn't recognize any of the functions they show as toolpack. I
tried one they said was toolpack =iseven(Number). It errored out with #Name.

Tomorrow I'll make a list of absolutely every funciton I use in this thing.

--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

This is my guess...

It's not the =replace() function itself that's causing what you see. You're
changing the formula in a cell. That means that every formula that depends on
that cell is going to recalculate.

And if there's a function from the analysis toolpak addin in your workbook that
is in a cell that's recalculating, then that could be the problem.

If you look at Tools|Addins, you'll see if you have the analysis toolpak addin
checked. If that's unchecked, then the formulas that rely on those functions
should break. (Maybe close excel and reopen, then reopen your workbook to see
it.)

There are a lot of functions in that analysis toolpak.

Peter Noneley's Excel Function Dictionary has lots of information about excel
functions:
http://www.xlfdic.com

Norman Harker has his version at Debra Dalgleish's site:
http://www.contextures.com/functions.html

Norman's looks like a more complete list.

Karen53 wrote:

Hi Dave,

Let me see if I understand correctly. It's not the 'replace' command itself
but the fact that 'replace' changes the formula causing this toolpak entity
to recalc?

I know nothing about the analysis toolpack. Is there something online that
lists what is in it so I know what I am looking for? Is it like a group of
commands, something I might have called not knowing it's toolpack? I tried a
help search in VBA but the toolpack does not come up.
--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

I'm not sure if it's really the =replace() worksheet function.

I tried this simple test:

Option Explicit
Sub testme()
Dim myCell As Range

Application.ScreenUpdating = False
Debug.Print "After step 001: " & Application.ScreenUpdating

Set myCell = ActiveSheet.Range("A1")
Debug.Print "After step 002: " & Application.ScreenUpdating

myCell.Formula = "=REPLACE(B1,1,3,""xx"")"
Debug.Print "After step 003: " & Application.ScreenUpdating

Application.ScreenUpdating = True
Debug.Print "After step 004: " & Application.ScreenUpdating

End Sub

And got:

After step 001: False
After step 002: False
After step 003: False
After step 004: True

But my guess is that you have something from the analysis toolpak on that sheet
(or in that workbook) that recalcs after you change the formula. And that call
to the analyis toolpak turns on .screenupdating.

Your next challenge <vbg is to search for that offending "something".


Karen53 wrote:

Hi Dave,

I discovered this while trying to trouble shoot something else. Earlier in
this post we were talking about volatile code. I've found "Replace" in the
code changes the screenupdating status to True.

Sub AddFormulaLinks(ShName, NextRow)

' add the formulas to link Main Page to the new sheet

Debug.Print "Starting AddFormulaLinks " & Application.ScreenUpdating

Dim iCtr As Long
Dim Cell As Range


Sheets(ShName).Activate

With Sheets(ShName)

'Unit Status Current? from Main Page =IF(ISBLANK('Master
Page'!$B$16),"", 'Master Page'!$B$14)
With .Range("B1")
.HorizontalAlignment = xlCenter
Debug.Print "AddFormulaLinks center " &
Application.ScreenUpdating

.WrapText = False
Debug.Print "AddFormulaLinks WrapText " &
Application.ScreenUpdating

.NumberFormat = "General"
Debug.Print "AddFormulaLinks No Format General " &
Application.ScreenUpdating

.FormulaR1C1 = "=If(ISBLANK('" & Replace(MainPagepg.Name, "'",
"''") & "'!R" _
& NextRow & "C2) ,"""", '" &
Replace(MainPagepg.Name, "'", "''") & "'!R" & NextRow & "C2)"
Debug.Print "AddFormulaLinks Replace " &
Application.ScreenUpdating

End With

Immediate window:
AddSheet call AddFormulaLinks False
Starting AddFormulaLinks False
AddFormulaLinks center False
AddFormulaLinks WrapText False
AddFormulaLinks No Format General False
AddFormulaLinks Replace True

--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

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

Sorry.

Karen53 wrote:

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?

--
Thanks for your help.
Karen53

"Karen53" wrote:

Hi Gord,

Never mind. The sheet was protected. The result is no conditional
formating. Back to my search...
--
Thanks for your help.
Karen53


"Gord Dibben" wrote:

F5SpecialConditional Formats


Gord Dibben MS Excel MVP

On Tue, 19 Feb 2008 10:41:01 -0800, Karen53
wrote:

Is there
a way to locate any conditional formating on a worksheet?



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Workbook Open
 

The times I've seen the calls to the analysis toolpak functions cause the
problem you see is when the function is called via VBA (I think???).

I'm sure that there are billions and billions of other resource, but I don't
have any other specific suggestions.

Sorry.

Karen53 wrote:

Hi Dave,

Do you feel this is most likely coming from VBA Code or a formiula on a
worksheet?

LOL, forget the Mad Cow! Right now I wish Scotty could beam me up!

I'm out of time. I've got to have this finished and I have absolutely no
idea how to find this problem. It is majorly impacting the workbooks. Are
there any other resources or options that I may not know about? What does
one do when you can't find the problem?

What I need is a tutor. Someone who can find the problem, help me fix it
and show me so I can learn from it.

Any suggestions?
--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

If the analysis toolpak isn't checked, then you're not using it. So I don't
think I'd spend much time going down that road.

But I still don't have a better guess.

Karen53 wrote:

Hi Dave,

Thank you so much for those links!

I've opened and closed excel several times and opened the workbook several
times. Analysis Toolpack is not check under ToolsAddins.

Offhand I didn't recognize any of the functions they show as toolpack. I
tried one they said was toolpack =iseven(Number). It errored out with #Name.

Tomorrow I'll make a list of absolutely every funciton I use in this thing.

--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

This is my guess...

It's not the =replace() function itself that's causing what you see. You're
changing the formula in a cell. That means that every formula that depends on
that cell is going to recalculate.

And if there's a function from the analysis toolpak addin in your workbook that
is in a cell that's recalculating, then that could be the problem.

If you look at Tools|Addins, you'll see if you have the analysis toolpak addin
checked. If that's unchecked, then the formulas that rely on those functions
should break. (Maybe close excel and reopen, then reopen your workbook to see
it.)

There are a lot of functions in that analysis toolpak.

Peter Noneley's Excel Function Dictionary has lots of information about excel
functions:
http://www.xlfdic.com

Norman Harker has his version at Debra Dalgleish's site:
http://www.contextures.com/functions.html

Norman's looks like a more complete list.

Karen53 wrote:

Hi Dave,

Let me see if I understand correctly. It's not the 'replace' command itself
but the fact that 'replace' changes the formula causing this toolpak entity
to recalc?

I know nothing about the analysis toolpack. Is there something online that
lists what is in it so I know what I am looking for? Is it like a group of
commands, something I might have called not knowing it's toolpack? I tried a
help search in VBA but the toolpack does not come up.
--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

I'm not sure if it's really the =replace() worksheet function.

I tried this simple test:

Option Explicit
Sub testme()
Dim myCell As Range

Application.ScreenUpdating = False
Debug.Print "After step 001: " & Application.ScreenUpdating

Set myCell = ActiveSheet.Range("A1")
Debug.Print "After step 002: " & Application.ScreenUpdating

myCell.Formula = "=REPLACE(B1,1,3,""xx"")"
Debug.Print "After step 003: " & Application.ScreenUpdating

Application.ScreenUpdating = True
Debug.Print "After step 004: " & Application.ScreenUpdating

End Sub

And got:

After step 001: False
After step 002: False
After step 003: False
After step 004: True

But my guess is that you have something from the analysis toolpak on that sheet
(or in that workbook) that recalcs after you change the formula. And that call
to the analyis toolpak turns on .screenupdating.

Your next challenge <vbg is to search for that offending "something".


Karen53 wrote:

Hi Dave,

I discovered this while trying to trouble shoot something else. Earlier in
this post we were talking about volatile code. I've found "Replace" in the
code changes the screenupdating status to True.

Sub AddFormulaLinks(ShName, NextRow)

' add the formulas to link Main Page to the new sheet

Debug.Print "Starting AddFormulaLinks " & Application.ScreenUpdating

Dim iCtr As Long
Dim Cell As Range


Sheets(ShName).Activate

With Sheets(ShName)

'Unit Status Current? from Main Page =IF(ISBLANK('Master
Page'!$B$16),"", 'Master Page'!$B$14)
With .Range("B1")
.HorizontalAlignment = xlCenter
Debug.Print "AddFormulaLinks center " &
Application.ScreenUpdating

.WrapText = False
Debug.Print "AddFormulaLinks WrapText " &
Application.ScreenUpdating

.NumberFormat = "General"
Debug.Print "AddFormulaLinks No Format General " &
Application.ScreenUpdating

.FormulaR1C1 = "=If(ISBLANK('" & Replace(MainPagepg.Name, "'",
"''") & "'!R" _
& NextRow & "C2) ,"""", '" &
Replace(MainPagepg.Name, "'", "''") & "'!R" & NextRow & "C2)"
Debug.Print "AddFormulaLinks Replace " &
Application.ScreenUpdating

End With

Immediate window:
AddSheet call AddFormulaLinks False
Starting AddFormulaLinks False
AddFormulaLinks center False
AddFormulaLinks WrapText False
AddFormulaLinks No Format General False
AddFormulaLinks Replace True

--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

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

Sorry.

Karen53 wrote:

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?

--
Thanks for your help.
Karen53

"Karen53" wrote:

Hi Gord,

Never mind. The sheet was protected. The result is no conditional
formating. Back to my search...
--
Thanks for your help.
Karen53


"Gord Dibben" wrote:

F5SpecialConditional Formats


Gord Dibben MS Excel MVP

On Tue, 19 Feb 2008 10:41:01 -0800, Karen53
wrote:

Is there
a way to locate any conditional formating on a worksheet?



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Workbook Open
 

Hi Dave,

I thought you might like to know what the cause was.

The Worksheet Calculates all running on workbook open was formuluas using
the 'Indirect' function. I had removed them to test but I had a wayward one
I didn't realize was there. I've removed them and this has stopped.

The screenupdating changing to True after the coded 'Replace' statement was
caused because the 'Replace' functions generated a 'hard' change on the
worksheet triggering the worksheet change rather than the worksheet
calculate. Once I removed the screenupdating code from worksheet change,
this was resolved.
--
Thanks for your help.
Karen53


"Dave Peterson" wrote:

If the analysis toolpak isn't checked, then you're not using it. So I don't
think I'd spend much time going down that road.

But I still don't have a better guess.

Karen53 wrote:

Hi Dave,

Thank you so much for those links!

I've opened and closed excel several times and opened the workbook several
times. Analysis Toolpack is not check under ToolsAddins.

Offhand I didn't recognize any of the functions they show as toolpack. I
tried one they said was toolpack =iseven(Number). It errored out with #Name.

Tomorrow I'll make a list of absolutely every funciton I use in this thing.

--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

This is my guess...

It's not the =replace() function itself that's causing what you see. You're
changing the formula in a cell. That means that every formula that depends on
that cell is going to recalculate.

And if there's a function from the analysis toolpak addin in your workbook that
is in a cell that's recalculating, then that could be the problem.

If you look at Tools|Addins, you'll see if you have the analysis toolpak addin
checked. If that's unchecked, then the formulas that rely on those functions
should break. (Maybe close excel and reopen, then reopen your workbook to see
it.)

There are a lot of functions in that analysis toolpak.

Peter Noneley's Excel Function Dictionary has lots of information about excel
functions:
http://www.xlfdic.com

Norman Harker has his version at Debra Dalgleish's site:
http://www.contextures.com/functions.html

Norman's looks like a more complete list.

Karen53 wrote:

Hi Dave,

Let me see if I understand correctly. It's not the 'replace' command itself
but the fact that 'replace' changes the formula causing this toolpak entity
to recalc?

I know nothing about the analysis toolpack. Is there something online that
lists what is in it so I know what I am looking for? Is it like a group of
commands, something I might have called not knowing it's toolpack? I tried a
help search in VBA but the toolpack does not come up.
--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

I'm not sure if it's really the =replace() worksheet function.

I tried this simple test:

Option Explicit
Sub testme()
Dim myCell As Range

Application.ScreenUpdating = False
Debug.Print "After step 001: " & Application.ScreenUpdating

Set myCell = ActiveSheet.Range("A1")
Debug.Print "After step 002: " & Application.ScreenUpdating

myCell.Formula = "=REPLACE(B1,1,3,""xx"")"
Debug.Print "After step 003: " & Application.ScreenUpdating

Application.ScreenUpdating = True
Debug.Print "After step 004: " & Application.ScreenUpdating

End Sub

And got:

After step 001: False
After step 002: False
After step 003: False
After step 004: True

But my guess is that you have something from the analysis toolpak on that sheet
(or in that workbook) that recalcs after you change the formula. And that call
to the analyis toolpak turns on .screenupdating.

Your next challenge <vbg is to search for that offending "something".


Karen53 wrote:

Hi Dave,

I discovered this while trying to trouble shoot something else. Earlier in
this post we were talking about volatile code. I've found "Replace" in the
code changes the screenupdating status to True.

Sub AddFormulaLinks(ShName, NextRow)

' add the formulas to link Main Page to the new sheet

Debug.Print "Starting AddFormulaLinks " & Application.ScreenUpdating

Dim iCtr As Long
Dim Cell As Range


Sheets(ShName).Activate

With Sheets(ShName)

'Unit Status Current? from Main Page =IF(ISBLANK('Master
Page'!$B$16),"", 'Master Page'!$B$14)
With .Range("B1")
.HorizontalAlignment = xlCenter
Debug.Print "AddFormulaLinks center " &
Application.ScreenUpdating

.WrapText = False
Debug.Print "AddFormulaLinks WrapText " &
Application.ScreenUpdating

.NumberFormat = "General"
Debug.Print "AddFormulaLinks No Format General " &
Application.ScreenUpdating

.FormulaR1C1 = "=If(ISBLANK('" & Replace(MainPagepg.Name, "'",
"''") & "'!R" _
& NextRow & "C2) ,"""", '" &
Replace(MainPagepg.Name, "'", "''") & "'!R" & NextRow & "C2)"
Debug.Print "AddFormulaLinks Replace " &
Application.ScreenUpdating

End With

Immediate window:
AddSheet call AddFormulaLinks False
Starting AddFormulaLinks False
AddFormulaLinks center False
AddFormulaLinks WrapText False
AddFormulaLinks No Format General False
AddFormulaLinks Replace True

--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

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

Sorry.

Karen53 wrote:

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?

--
Thanks for your help.
Karen53

"Karen53" wrote:

Hi Gord,

Never mind. The sheet was protected. The result is no conditional
formating. Back to my search...
--
Thanks for your help.
Karen53


"Gord Dibben" wrote:

F5SpecialConditional Formats


Gord Dibben MS Excel MVP

On Tue, 19 Feb 2008 10:41:01 -0800, Karen53
wrote:

Is there
a way to locate any conditional formating on a worksheet?



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Workbook Open
 

Glad you found the answer. And thanks for posting back.

Karen53 wrote:

Hi Dave,

I thought you might like to know what the cause was.

The Worksheet Calculates all running on workbook open was formuluas using
the 'Indirect' function. I had removed them to test but I had a wayward one
I didn't realize was there. I've removed them and this has stopped.

The screenupdating changing to True after the coded 'Replace' statement was
caused because the 'Replace' functions generated a 'hard' change on the
worksheet triggering the worksheet change rather than the worksheet
calculate. Once I removed the screenupdating code from worksheet change,
this was resolved.
--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

If the analysis toolpak isn't checked, then you're not using it. So I don't
think I'd spend much time going down that road.

But I still don't have a better guess.

Karen53 wrote:

Hi Dave,

Thank you so much for those links!

I've opened and closed excel several times and opened the workbook several
times. Analysis Toolpack is not check under ToolsAddins.

Offhand I didn't recognize any of the functions they show as toolpack. I
tried one they said was toolpack =iseven(Number). It errored out with #Name.

Tomorrow I'll make a list of absolutely every funciton I use in this thing.

--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

This is my guess...

It's not the =replace() function itself that's causing what you see. You're
changing the formula in a cell. That means that every formula that depends on
that cell is going to recalculate.

And if there's a function from the analysis toolpak addin in your workbook that
is in a cell that's recalculating, then that could be the problem.

If you look at Tools|Addins, you'll see if you have the analysis toolpak addin
checked. If that's unchecked, then the formulas that rely on those functions
should break. (Maybe close excel and reopen, then reopen your workbook to see
it.)

There are a lot of functions in that analysis toolpak.

Peter Noneley's Excel Function Dictionary has lots of information about excel
functions:
http://www.xlfdic.com

Norman Harker has his version at Debra Dalgleish's site:
http://www.contextures.com/functions.html

Norman's looks like a more complete list.

Karen53 wrote:

Hi Dave,

Let me see if I understand correctly. It's not the 'replace' command itself
but the fact that 'replace' changes the formula causing this toolpak entity
to recalc?

I know nothing about the analysis toolpack. Is there something online that
lists what is in it so I know what I am looking for? Is it like a group of
commands, something I might have called not knowing it's toolpack? I tried a
help search in VBA but the toolpack does not come up.
--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

I'm not sure if it's really the =replace() worksheet function.

I tried this simple test:

Option Explicit
Sub testme()
Dim myCell As Range

Application.ScreenUpdating = False
Debug.Print "After step 001: " & Application.ScreenUpdating

Set myCell = ActiveSheet.Range("A1")
Debug.Print "After step 002: " & Application.ScreenUpdating

myCell.Formula = "=REPLACE(B1,1,3,""xx"")"
Debug.Print "After step 003: " & Application.ScreenUpdating

Application.ScreenUpdating = True
Debug.Print "After step 004: " & Application.ScreenUpdating

End Sub

And got:

After step 001: False
After step 002: False
After step 003: False
After step 004: True

But my guess is that you have something from the analysis toolpak on that sheet
(or in that workbook) that recalcs after you change the formula. And that call
to the analyis toolpak turns on .screenupdating.

Your next challenge <vbg is to search for that offending "something".


Karen53 wrote:

Hi Dave,

I discovered this while trying to trouble shoot something else. Earlier in
this post we were talking about volatile code. I've found "Replace" in the
code changes the screenupdating status to True.

Sub AddFormulaLinks(ShName, NextRow)

' add the formulas to link Main Page to the new sheet

Debug.Print "Starting AddFormulaLinks " & Application.ScreenUpdating

Dim iCtr As Long
Dim Cell As Range


Sheets(ShName).Activate

With Sheets(ShName)

'Unit Status Current? from Main Page =IF(ISBLANK('Master
Page'!$B$16),"", 'Master Page'!$B$14)
With .Range("B1")
.HorizontalAlignment = xlCenter
Debug.Print "AddFormulaLinks center " &
Application.ScreenUpdating

.WrapText = False
Debug.Print "AddFormulaLinks WrapText " &
Application.ScreenUpdating

.NumberFormat = "General"
Debug.Print "AddFormulaLinks No Format General " &
Application.ScreenUpdating

.FormulaR1C1 = "=If(ISBLANK('" & Replace(MainPagepg.Name, "'",
"''") & "'!R" _
& NextRow & "C2) ,"""", '" &
Replace(MainPagepg.Name, "'", "''") & "'!R" & NextRow & "C2)"
Debug.Print "AddFormulaLinks Replace " &
Application.ScreenUpdating

End With

Immediate window:
AddSheet call AddFormulaLinks False
Starting AddFormulaLinks False
AddFormulaLinks center False
AddFormulaLinks WrapText False
AddFormulaLinks No Format General False
AddFormulaLinks Replace True

--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

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

Sorry.

Karen53 wrote:

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?

--
Thanks for your help.
Karen53

"Karen53" wrote:

Hi Gord,

Never mind. The sheet was protected. The result is no conditional
formating. Back to my search...
--
Thanks for your help.
Karen53


"Gord Dibben" wrote:

F5SpecialConditional Formats


Gord Dibben MS Excel MVP

On Tue, 19 Feb 2008 10:41:01 -0800, Karen53
wrote:

Is there
a way to locate any conditional formating on a worksheet?



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson