Deactivate Formula
 

I have a worksheet that has a circualr reference in it. I need a macro or
code so I can deactivate the formula on close.
I have set up a code to set my interations on opening the file but the
spreadsheet does its calcualtions before my interation macro runs, so the
user gets a circualr reference message. It has been suggested to me to
deactivate the formula on close and then reactivate it after my interation
macro ran would work. The problem is, I am too dumb to figure out how to
deactivate the formula. Any help or advise would be appreciated. Thanks!

Deactivate Formula
 

You could just put a space in front of it on close, and remove that on
start-up. Let's assume the formula is in Sheet1 Range ("A1"), this code does
it

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Worksheets("Sheet2").Range("A1")
.Formula = " " & .Formula
End With

End Sub

Private Sub Workbook_Open()
With Worksheets("Sheet2").Range("A1")
If Left(.Value, 1) = " " Then
.Formula = Right(.Value, Len(.Value) - 1)
End If
End With
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"DME" <cschelskeatdmeraildotcom wrote in message
...
I have a worksheet that has a circualr reference in it. I need a macro or
code so I can deactivate the formula on close.
I have set up a code to set my interations on opening the file but the
spreadsheet does its calcualtions before my interation macro runs, so the
user gets a circualr reference message. It has been suggested to me to
deactivate the formula on close and then reactivate it after my interation
macro ran would work. The problem is, I am too dumb to figure out how to
deactivate the formula. Any help or advise would be appreciated. Thanks!

Deactivate Formula
 

Thanks Bob! This worked great!
"Bob Phillips" wrote in message
...
You could just put a space in front of it on close, and remove that on
start-up. Let's assume the formula is in Sheet1 Range ("A1"), this code
does
it

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Worksheets("Sheet2").Range("A1")
.Formula = " " & .Formula
End With

End Sub

Private Sub Workbook_Open()
With Worksheets("Sheet2").Range("A1")
If Left(.Value, 1) = " " Then
.Formula = Right(.Value, Len(.Value) - 1)
End If
End With
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"DME" <cschelskeatdmeraildotcom wrote in message
...
I have a worksheet that has a circualr reference in it. I need a macro
or
code so I can deactivate the formula on close.
I have set up a code to set my interations on opening the file but the
spreadsheet does its calcualtions before my interation macro runs, so
the
user gets a circualr reference message. It has been suggested to me to
deactivate the formula on close and then reactivate it after my
interation
macro ran would work. The problem is, I am too dumb to figure out how
to
deactivate the formula. Any help or advise would be appreciated.
Thanks!