Hi

I have a spreadsheet which requires iterative calculation. I have written
code which gives the user the option to automatically enable iterative
calculation through selection of a series of message boxes (see below).

Private Sub Workbook_Open()
returnvalue2 = 7
Do While returnvalue2 = 7
returnvalue1 = MsgBox("Before using this sheet ensure that ITERATION
is switched on!" & Chr(13) & "Would you like this to be automatically
activated?", vbExclamation + vbYesNo, "Important")
If returnvalue1 = 6 Then
returnvalue2 = 0
Application.Iteration = True
Application.Calculation = xlCalculationAutomatic
Exit Do
Else
returnvalue2 = MsgBox("Not activating ITERATIVE CALCULATION will
cause the spreadsheet to give incorrect values." & Chr(13) & "Are you sure
you do not want to activate ITERATIVE CALCULATION?", vbCritical + vbYesNo,
"VERY IMPORTANT")
If returnvalue2 = 6 Then
Exit Do
End If
End If
Loop
End Sub

This works perfectly except that when the workbook is opened, the excel
circular reference warning comes up automatically if iterative calculation is
not active. And then excel help opens and things just get out of hand with
messageboxes popping up all over the place. What I'd like to do is interrupt
the workbook open event and allow my code to run before any built-in checks.
How do I do this?

Maybe you can use a "helper" workbook that changes the setting, then opens the
real workbook, and then closes itself.



BSc Chem Eng Rick wrote:

Hi

I have a spreadsheet which requires iterative calculation. I have written
code which gives the user the option to automatically enable iterative
calculation through selection of a series of message boxes (see below).

Private Sub Workbook_Open()
returnvalue2 = 7
Do While returnvalue2 = 7
returnvalue1 = MsgBox("Before using this sheet ensure that ITERATION
is switched on!" & Chr(13) & "Would you like this to be automatically
activated?", vbExclamation + vbYesNo, "Important")
If returnvalue1 = 6 Then
returnvalue2 = 0
Application.Iteration = True
Application.Calculation = xlCalculationAutomatic
Exit Do
Else
returnvalue2 = MsgBox("Not activating ITERATIVE CALCULATION will
cause the spreadsheet to give incorrect values." & Chr(13) & "Are you sure
you do not want to activate ITERATIVE CALCULATION?", vbCritical + vbYesNo,
"VERY IMPORTANT")
If returnvalue2 = 6 Then
Exit Do
End If
End If
Loop
End Sub

This works perfectly except that when the workbook is opened, the excel
circular reference warning comes up automatically if iterative calculation is
not active. And then excel help opens and things just get out of hand with
messageboxes popping up all over the place. What I'd like to do is interrupt
the workbook open event and allow my code to run before any built-in checks.
How do I do this?

--

Dave Peterson

Thanks Dave

I had thought of implementing a "helper". the only problem is it generates
another file in the directory. On a network based system it is difficult to
communicate to everyone that may open the folder that the "helper" file is
not to be deleted and must be copied along with the master. I don't want to
hide the master file because firstly most people have "View Hidden Files or
Folders" active and people also tend to reuse spreadsheets for different
projects.

Is there a way to embed a workbook within another workbook i.e. two
worbooks, one excel file?

"Dave Peterson" wrote:

Maybe you can use a "helper" workbook that changes the setting, then opens the
real workbook, and then closes itself.



BSc Chem Eng Rick wrote:

Hi

I have a spreadsheet which requires iterative calculation. I have written
code which gives the user the option to automatically enable iterative
calculation through selection of a series of message boxes (see below).

Private Sub Workbook_Open()
returnvalue2 = 7
Do While returnvalue2 = 7
returnvalue1 = MsgBox("Before using this sheet ensure that ITERATION
is switched on!" & Chr(13) & "Would you like this to be automatically
activated?", vbExclamation + vbYesNo, "Important")
If returnvalue1 = 6 Then
returnvalue2 = 0
Application.Iteration = True
Application.Calculation = xlCalculationAutomatic
Exit Do
Else
returnvalue2 = MsgBox("Not activating ITERATIVE CALCULATION will
cause the spreadsheet to give incorrect values." & Chr(13) & "Are you sure
you do not want to activate ITERATIVE CALCULATION?", vbCritical + vbYesNo,
"VERY IMPORTANT")
If returnvalue2 = 6 Then
Exit Do
End If
End If
Loop
End Sub

This works perfectly except that when the workbook is opened, the excel
circular reference warning comes up automatically if iterative calculation is
not active. And then excel help opens and things just get out of hand with
messageboxes popping up all over the place. What I'd like to do is interrupt
the workbook open event and allow my code to run before any built-in checks.
How do I do this?

--

Dave Peterson

Not that I know.

BSc Chem Eng Rick wrote:

Thanks Dave

I had thought of implementing a "helper". the only problem is it generates
another file in the directory. On a network based system it is difficult to
communicate to everyone that may open the folder that the "helper" file is
not to be deleted and must be copied along with the master. I don't want to
hide the master file because firstly most people have "View Hidden Files or
Folders" active and people also tend to reuse spreadsheets for different
projects.

Is there a way to embed a workbook within another workbook i.e. two
worbooks, one excel file?

"Dave Peterson" wrote:

Maybe you can use a "helper" workbook that changes the setting, then opens the
real workbook, and then closes itself.



BSc Chem Eng Rick wrote:

Hi

I have a spreadsheet which requires iterative calculation. I have written
code which gives the user the option to automatically enable iterative
calculation through selection of a series of message boxes (see below).

Private Sub Workbook_Open()
returnvalue2 = 7
Do While returnvalue2 = 7
returnvalue1 = MsgBox("Before using this sheet ensure that ITERATION
is switched on!" & Chr(13) & "Would you like this to be automatically
activated?", vbExclamation + vbYesNo, "Important")
If returnvalue1 = 6 Then
returnvalue2 = 0
Application.Iteration = True
Application.Calculation = xlCalculationAutomatic
Exit Do
Else
returnvalue2 = MsgBox("Not activating ITERATIVE CALCULATION will
cause the spreadsheet to give incorrect values." & Chr(13) & "Are you sure
you do not want to activate ITERATIVE CALCULATION?", vbCritical + vbYesNo,
"VERY IMPORTANT")
If returnvalue2 = 6 Then
Exit Do
End If
End If
Loop
End Sub

This works perfectly except that when the workbook is opened, the excel
circular reference warning comes up automatically if iterative calculation is
not active. And then excel help opens and things just get out of hand with
messageboxes popping up all over the place. What I'd like to do is interrupt
the workbook open event and allow my code to run before any built-in checks.
How do I do this?

--

Dave Peterson


--

Dave Peterson