I am using Chip Pearsons recommended code snippet to see
if a cell is circular:

If Not Application.Intersect(rng, rng.Precedents) Is
Nothing Then
CellIsCircular = True
End If

However it fails when it comes across a cell which does
not have a formula. So I added a test for a formula:

If rng.HasFormula = True Then
If Not Application.Intersect(rng, rng.Precedents) Is
Nothing Then
CellIsCircular = True
Else
CellIsCircular = False
End If
End If
End Function

However it seems to fail when indeed there is a precedent
but not on the same sheet as the cell. Is this becauase
the precedent property on list precedents on the same
sheet as the cell in question?

to the best of my knowledge, rng.precedents only returns precedents which
are on the same sheet.

Stephen Bullen has already written code that evaluates circular references
and you can download it from his site. If this is not a commercial venture,
you might check that out. If it is something you plan on selling, perhaps
you can contact him and license his code.

htttp://www.oaltd.co.uk/Excel/Default.htm

It does handle other sheet precedents I am sure.

--
Regards,
Tom Ogilvy

"ExcelMonkey" wrote in message
...
I am using Chip Pearsons recommended code snippet to see
if a cell is circular:

If Not Application.Intersect(rng, rng.Precedents) Is
Nothing Then
CellIsCircular = True
End If

However it fails when it comes across a cell which does
not have a formula. So I added a test for a formula:

If rng.HasFormula = True Then
If Not Application.Intersect(rng, rng.Precedents) Is
Nothing Then
CellIsCircular = True
Else
CellIsCircular = False
End If
End If
End Function

However it seems to fail when indeed there is a precedent
but not on the same sheet as the cell. Is this becauase
the precedent property on list precedents on the same
sheet as the cell in question?

Thanks Tom. I went to Stephen Bullens old website, not
knowing that it had changed, and got a little more than I
bargained for! Not exactly an Excel site! Thanks for
the help.

EM


-----Original Message-----
to the best of my knowledge, rng.precedents only returns
precedents which
are on the same sheet.

Stephen Bullen has already written code that evaluates
circular references
and you can download it from his site. If this is not a
commercial venture,
you might check that out. If it is something you plan
on selling, perhaps
you can contact him and license his code.

htttp://www.oaltd.co.uk/Excel/Default.htm

It does handle other sheet precedents I am sure.

--
Regards,
Tom Ogilvy

"ExcelMonkey"
wrote in message
...
I am using Chip Pearsons recommended code snippet to
see
if a cell is circular:

If Not Application.Intersect(rng, rng.Precedents) Is
Nothing Then
CellIsCircular = True
End If

However it fails when it comes across a cell which does
not have a formula. So I added a test for a formula:

If rng.HasFormula = True Then
If Not Application.Intersect(rng, rng.Precedents)
Is
Nothing Then
CellIsCircular = True
Else
CellIsCircular = False
End If
End If
End Function

However it seems to fail when indeed there is a
precedent
but not on the same sheet as the cell. Is this
becauase
the precedent property on list precedents on the same
sheet as the cell in question?


.