Getting grip on dependents

Hi,
In my spreadsheet ther is a field that has a dependent in an other
worksheet.
How can I find that.
the VB property 'dependents' will only return dependents on the same
worksheet.

best rgds,
Ton

Ton,

It's a nasty one. A small extract of a much much bigger routine kludged
quickly purely to demonstrate how to test it. Might be bugs in this, since I
haven't tried doing anything with it.

'you would probably set up some kind of loop here but for the time being
Set rngTestCell = cells(1,1)

'use the showdependents method then try and navigate along the arrows
rngTestCell.ShowDependents
On Error GoTo 0
nLinkNumber = 1
Do
On Error GoTo NoMoreLinks
rngTestCell.NavigateArrow TowardPrecedent:=False, ArrowNumber:=1,
LinkNumber:=nLinkNumber
On Error Goto 0

'writing this as I go in this message
if not activecell.parent is rngTestCell.Parent then
msgbox activecell.parent.name & " " & activecell.address

'you would probably reselect the original sheet here, e.g.
rngTestCell.Parent.Select

End If
nLinkNumber = nLinkNumber +1
Loop


Exit Sub
NoMoreLinks:
'if you got here the cell doesn't have an external dependent
'resume somewhere back in your code
End Sub

I've pushed all this to the limit in my XspandXL add-in on my site if you
are interested. It traces external dependents, precedents, circularity, all
sorts of things really. There's a 30 day demo if you want to save some time.

Robin Hammond
www.enhanceddatasystems.com

"tondewilde" <[email protected]> wrote in message
news:%[email protected]...
> Hi,
> In my spreadsheet ther is a field that has a dependent in an other
> worksheet.
> How can I find that.
> the VB property 'dependents' will only return dependents on the same
> worksheet.
>
> best rgds,
> Ton
>
>