Eliminate Excel Circular Reference Warning

Hi all. I am trying to disable or deactivate the formulas in several cells as part of the workbook close event and reactive them during the workbook open event. The formulas contain an intentional circular reference and I'm trying to convert them to a text string so they won't be recognized as a circular reference when the workbook is opened again until after I enable iteration as part of the open event, after which I want to convert the text strings back to formulas. The purpose of this is to avoid the circular reference warning when the workbook is opened by users. I continue to get an error message in my code at the cell.formula lines in both of my subs below. Any help would be appreciated!

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Many thanks in advance.

"Range" does not have a object, method or attribute called "cell" so you will get an error . "Range" and "Cells" is generally an either or situation .. either you refer to a cells by the "Cells" object OR the "Range" object.
... maybe this code would help ? ... it's format the cells so they are text or general ..

Please Login or Register  to view this content.

Thanks for your help and clarification. I have replaced my code with the subs you created and have run them in my Workbook_Open() and Workbook_BeforeClose subs as shown below. They change the number format back and forth as intended. However, this still does not prevent Excel from giving the Circular Reference Warning upon opening the workbook, which is my ultimate objective.

When viewing the formulas in the formula bar after the cell format has been changed to "text" via the code, there is no evidence that the formula has been converted to a text string, which is what I may need to avoid the circular reference warning?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Again, thanks for your help.

Thanks Shg.

I've added the suggested code and it was successful in eliminating the circular reference warning. But it also had an unintended consequence on the circular reference formulas in the worksheet by causing them to return a "0" value or deleting the formulas altogether.

I've attached a sample worksheet with the imbedded code, the circular reference formulas, and an explanation of what I need to achieve. I'm sure there is a solution here that I'm just not seeing.

Many thanks for the help!

Bump no response