Shankfoot,

The only cell that seems to have a circular reference is D20 so I would
suggest changing the formula to a constant as follows:

First of all copy the formula in D20 and paste it into cell H20, (or any
other cell but you will have to change the references in the Macros to
suite), then hide column H

In the This Workbook Module change your Macro to:

Private Sub Workbook_Open()
Application.Iteration = True
Application.EnableEvents = False
CalculateIt
Application.EnableEvents = True
End Sub

and add:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Iteration = False
End Sub

Next in a normal Module add:

Sub CalculateIt()
With Sheets("Lease Worksheet")
.Range("H20").Copy .Range("D20")
.Calculate
.Range("D20").Copy
.Range("D20").PasteSpecial _
Paste:=xlValues
Application.CutCopyMode = False
End With
End Sub

Right-click on the "Data Entry" tab and select "View Code" and enter in the
sheet Module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.ScreenUpdating = False
Application.EnableEvents = False
CalculateIt
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Next do the same to the "Lease Worksheet" and insert the same code as in
"Data Entry"

The sheet should then calculate without any *Calculate* or *Circular
Reference* alerts

--
HTH

Sandy

with @tiscali.co.uk
"sharkfoot" wrote
in message ...

I have a workbook (attached to the message so take a peek) with a
circular reference that, apparently, has got to be there. I added a
private sub to make sure the sheet keeps the ITERATION checkbox
selected:


Code:
--------------------
Private Sub Workbook_Open()
Application.Iteration = True
End Sub
--------------------


One problem still is that the sub apparently goes into effect AFTER the
workbook checks for circular references. What happens is that the person
opening the workbook gets the "excel cannot do this" message that is
rather unsightly and potentially confusing to the user.

How do I:

A. Get rid of (supress?) the warning box that appears or
B. Have the sub get called upon BEFORE the workbook checks for circular
references?
C. Figure out a way to avoid the circular reference altogether.

Anything would be good at this point. Please help!


+-------------------------------------------------------------------+
|Filename: lease.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4516 |
+-------------------------------------------------------------------+

--
sharkfoot
------------------------------------------------------------------------
sharkfoot's Profile:
http://www.excelforum.com/member.php...o&userid=32164
View this thread: http://www.excelforum.com/showthread...hreadid=525887