Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deactivate Save As | Excel Discussion (Misc queries) | |||
deactivate the get pivot formula when I link a cell to a pivot | New Users to Excel | |||
Deactivate worksheet | Excel Discussion (Misc queries) | |||
Deactivate formula after a set time | Excel Discussion (Misc queries) | |||
Deactivate web toolbar | Excel Discussion (Misc queries) |