Deactivate Formula

  • Thread starter Thread starter DME
  • Start date Start date
D

DME

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!
 
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)
 
Thanks Bob! This worked great!
Bob Phillips said:
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)
Click to expand...
 
Back
Top