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

No one brave enough to take a stab at this?


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

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

Attached is the file after I made the changes you suggested. As you can
see, something is very, very wrong but I'm not sure what. Can you tell
me what went wrong?

Sandy Mann Wrote:
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



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

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

Hi Shankfoot,

What is wrong is that the formula you have in the hidden H20 is reading
=SUM(D12-D18) when it should be referencing H12 & H18.

I fixed it by:

Open the file with Macros disabled, unhide Column H and change the formula
in H20 to =H12-H18 (The SUM part is not required.)

Hide Column H again and put any number into D20. This will remove all the
#REF! errors.

Save the spreadsheet under another name and close it. Now open the new
spreadsheet again and enable Macros. After that it worked for me again.

(Incidentally the SUM part is not required in D12 either or you can change
it to =SUM(D6:D11), similarly D26 & D32 don't require a SUM either).

If you have any more trouble the do post back again.

--
HTH

Sandy

with @tiscali.co.uk


"sharkfoot" wrote
in message ...

Attached is the file after I made the changes you suggested. As you can
see, something is very, very wrong but I'm not sure what. Can you tell
me what went wrong?

Perhaps I should have explained why the worksheet went wrong for you.

It looks like you either copied only the SUM(D12-D18) without the = sign or
simply typed into cell H20 exactly what is in Cell D20.

If you simply click into a cell and click copy either by the toolbar button
or the right-click menu and then click into another cell and paste Excel
will automatically adjust the reference to the new location. For example in
cell H2 enter the formula =D2 Now click back into the cell and copy it and
paste into cell E2. The formula that you have just pasted into Cell E2 will
now be =A2. Excel changed the formula which was referencing a cell four
columns to the left of the original to be still referencing a cell four
columns to the left but not from the NEW location.

Next copy cell H2 again and now paste it into cell D2 - you will get a #REF!
error! Why? Because it is still referencing a cell four columns to the left
but now there is no cell four columns to the left of D2 so Excel alerts you
to this by giving you a #REF! error.

An exception to this is when you make the reference ABSOLUTE as in =$D$2.
This will always refer to cell D2 even if you paste it into cell A10. Look
up *Move or copy a formula* in Help.

By copying the formula in D20 to H20 in the first place it changes it from
=D12-D18 to =H12-H18. H12 and H18 are both empty so it will not create a
circular reference. However, when it gets copied back into D20 by the Macro
of course it does create the circular reference again but the Macro goes on
to paste the contents of the cell as a constant thus removing the circular
reference error once more.



--
HTH

Sandy

with @tiscali.co.uk
"Sandy Mann" wrote in message
...
Hi Shankfoot,

What is wrong is that the formula you have in the hidden H20 is reading
=SUM(D12-D18) when it should be referencing H12 & H18.

I fixed it by:

Open the file with Macros disabled, unhide Column H and change the formula
in H20 to =H12-H18 (The SUM part is not required.)

Hide Column H again and put any number into D20. This will remove all the
#REF! errors.

Save the spreadsheet under another name and close it. Now open the new
spreadsheet again and enable Macros. After that it worked for me again.

(Incidentally the SUM part is not required in D12 either or you can change
it to =SUM(D6:D11), similarly D26 & D32 don't require a SUM either).

If you have any more trouble the do post back again.

--
HTH

Sandy

with @tiscali.co.uk


"sharkfoot" wrote
in message ...

Attached is the file after I made the changes you suggested. As you can
see, something is very, very wrong but I'm not sure what. Can you tell
me what went wrong?

Awesome. I have one more question. I need to have this done 3 times in
this sheet. In other words, what do I need to change to have the D, H,
and L columns (we already have D working) all figure this same formula?
I have the formulas for D20 copied into N20. H20 copied into P20 and L20
copied into R20. I'm don't know if I need to add to the current module
or just add 2 more modules for each of the new target cells.

Can you tell me wat todo to get these other 2 leases working? Thanks!

Sandy Mann Wrote:
Perhaps I should have explained why the worksheet went wrong for you.

It looks like you either copied only the SUM(D12-D18) without the =
sign or
simply typed into cell H20 exactly what is in Cell D20.

If you simply click into a cell and click copy either by the toolbar
button
or the right-click menu and then click into another cell and paste
Excel
will automatically adjust the reference to the new location. For
example in
cell H2 enter the formula =D2 Now click back into the cell and copy it
and
paste into cell E2. The formula that you have just pasted into Cell E2
will
now be =A2. Excel changed the formula which was referencing a cell
four
columns to the left of the original to be still referencing a cell
four
columns to the left but not from the NEW location.

Next copy cell H2 again and now paste it into cell D2 - you will get a
#REF!
error! Why? Because it is still referencing a cell four columns to the
left
but now there is no cell four columns to the left of D2 so Excel alerts
you
to this by giving you a #REF! error.

An exception to this is when you make the reference ABSOLUTE as in
=$D$2.
This will always refer to cell D2 even if you paste it into cell A10.
Look
up *Move or copy a formula* in Help.

By copying the formula in D20 to H20 in the first place it changes it
from
=D12-D18 to =H12-H18. H12 and H18 are both empty so it will not create
a
circular reference. However, when it gets copied back into D20 by the
Macro
of course it does create the circular reference again but the Macro
goes on
to paste the contents of the cell as a constant thus removing the
circular
reference error once more.



--
HTH

Sandy

with @tiscali.co.uk



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

Hi Shankfoot,

Assuming that you will have a similar setup in the other two tables as you
have in column D so that the cells that end up with circular references a
D20, H20 & L20 then it should be fairly simple .

You will notice have that the formula that is in H20 only referred to Column
H so when it was copied to D20 it then only referred to Column D. This
means that we can copy the formula to N20 and use that one formula to copy
to cells D20, H20 &L20.

With Macros disabled, (otherwise they may change things back after you
change them), I unhid Column H, copied the formula in H20 to N20,(or you can
just type into N20 the forumula =N12-N18 Note that the foumula MUST refer to
the same column that the formula is in and the cell MUST be formatted the
same as you want cells D20, H20 & L20 to be because when the Macro pastes in
the new formula the formatting will automatically be changed to that of cell
N20. Also note that you don't need a formula in P20 or R20.), I then hid
Column N.

Next I changed the CalculateIt() Macro to:

Sub CalculateIt()

With Sheets("Lease Worksheet")
.Range("N20").Copy .Range("D20")
.Range("D20").Copy
.Range("D20").PasteSpecial Paste:=xlValues

.Range("N20").Copy .Range("H20")
.Range("H20").Copy
.Range("H20").PasteSpecial Paste:=xlValues

.Range("N20").Copy .Range("L20")
.Range("L20").Copy
.Range("L20").PasteSpecial Paste:=xlValues

Application.CutCopyMode = False

End With

End Sub

I tested it by creating two other tables with the calculations in Columns D,
H & L and all three tables updated as expected. I found by experimentation
that the .Calculate line that was in my original code was not required -
Excel calculates when the formulas are pasted in - although I am sure that
it wouldn't do so when I was originally trying out the code.

--
HTH

Sandy

with @tiscali.co.uk

"sharkfoot" wrote
in message ...

Awesome. I have one more question. I need to have this done 3 times in
this sheet. In other words, what do I need to change to have the D, H,
and L columns (we already have D working) all figure this same formula?
I have the formulas for D20 copied into N20. H20 copied into P20 and L20
copied into R20. I'm don't know if I need to add to the current module
or just add 2 more modules for each of the new target cells.

Can you tell me wat todo to get these other 2 leases working? Thanks!

Sandy Mann Wrote:
Perhaps I should have explained why the worksheet went wrong for you.

It looks like you either copied only the SUM(D12-D18) without the =
sign or
simply typed into cell H20 exactly what is in Cell D20.

If you simply click into a cell and click copy either by the toolbar
button
or the right-click menu and then click into another cell and paste
Excel
will automatically adjust the reference to the new location. For
example in
cell H2 enter the formula =D2 Now click back into the cell and copy it
and
paste into cell E2. The formula that you have just pasted into Cell E2
will
now be =A2. Excel changed the formula which was referencing a cell
four
columns to the left of the original to be still referencing a cell
four
columns to the left but not from the NEW location.

Next copy cell H2 again and now paste it into cell D2 - you will get a
#REF!
error! Why? Because it is still referencing a cell four columns to the
left
but now there is no cell four columns to the left of D2 so Excel alerts
you
to this by giving you a #REF! error.

An exception to this is when you make the reference ABSOLUTE as in
=$D$2.
This will always refer to cell D2 even if you paste it into cell A10.
Look
up *Move or copy a formula* in Help.

By copying the formula in D20 to H20 in the first place it changes it
from
=D12-D18 to =H12-H18. H12 and H18 are both empty so it will not create
a
circular reference. However, when it gets copied back into D20 by the
Macro
of course it does create the circular reference again but the Macro
goes on
to paste the contents of the cell as a constant thus removing the
circular
reference error once more.



--
HTH

Sandy

with @tiscali.co.uk



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

Sandy Mann Wrote:
Hi Shankfoot,

Assuming that you will have a similar setup in the other two tables as
you
have in column D so that the cells that end up with circular references
a
D20, H20 & L20 then it should be fairly simple .

Thank you so much. This works perfectly. And the way you explain things
helps me learn much better than someone just fixing the problem for me.
Thanks again!


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

You're welcome Sharkfoot,

"sharkfoot" wrote
in message ...

Thank you so much. This works perfectly. And the way you explain things
helps me learn much better than someone just fixing the problem for me.

"Sandy Mann" wrote in message
...

H & L and all three tables updated as expected. I found by
experimentation
that the .Calculate line that was in my original code was not required -
Excel calculates when the formulas are pasted in

Seems like we both learned for the experience.

--
Regards

Sandy

with @tiscali.co.uk