Dependents on another worksheet

  • Thread starter Thread starter tyeung4
  • Start date Start date
T

tyeung4

Hi,
The dependents property of a Range object only returns dependen
cells on the same worksheet. How do I collect dependent cells on othe
worksheets?

And what is the difference between Dependents and DirectDependents?

Thanks.

Teres
 
Teresa,

This is a very cut down extract from one of a series of complex tracing
routines you can find in my XspandXL add in on my site.

Have a look at the Tracing and Auditing section here:
http://www.enhanceddatasystems.com/ED/Pages/XspandXLAdditional.htm

To trace dependents on another sheet you need to use the following
technique. As I've produced it here, this only tests a single cell. You also
need to watch out that the dependents are not in hidden sheets, since Excel
will not follow the link to something hidden.

Sub Externals()
Dim rngTest as Range
Dim nLinkNumber as Integer
Dim shThisSheet as Worksheet

'show arrows which may or may not have external links
set rngTest = ActiveCell

'excel raises an error if there are no dependents so trap it
On Error Goto NoMoreLinks
rngTest.ShowDependents nLinkNumber = 1
On Error Goto 0

nLinkNumber = 1
Set shThisSheet = ActiveSheet
Do
'if there are no more links excel raises an error
On Error GoTo NoMoreLinks
rngTest.NavigateArrow TowardPrecedent:=False, ArrowNumber:=1,
LinkNumber:=nLinkNumber
On Error GoTo 0

'if you find the original cell there are no externals
If ActiveCell Is rngTest Then Exit Do

If Not ActiveCell.Parent Is shThisSheet Then
'you have found an external dependent
End If
nLinkNumber = nLinkNumber + 1
Loop

EndRoutine:
Exit Sub

NoMoreLinks:
On Error GoTo 0
Resume EndRoutine
End Sub

Robin Hammond
www.enhanceddatasystems.com
 
Addendum:
This gets rid of the arrows after the trace.

Sub Externals()
Dim rngTest As Range
Dim nLinkNumber As Integer
Dim shThisSheet As Worksheet

'show arrows which may or may not have external links
Set rngTest = ActiveCell

'excel raises an error if there are no dependents so trap it
On Error GoTo NoMoreLinks
rngTest.ShowDependents nLinkNumber = 1
On Error GoTo 0

nLinkNumber = 1
Set shThisSheet = ActiveSheet
Do
'if there are no more links excel raises an error
On Error GoTo NoMoreLinks
rngTest.NavigateArrow TowardPrecedent:=False, ArrowNumber:=1,
LinkNumber:=nLinkNumber
On Error GoTo 0

'if you find the original cell there are no externals
If ActiveCell Is rngTest Then Exit Do

If Not ActiveCell.Parent Is shThisSheet Then
MsgBox "this cell has external dependents"
End If
nLinkNumber = nLinkNumber + 1
Loop

EndRoutine:
shThisSheet.Activate
rngTest.Select
ActiveSheet.ClearArrows
Exit Sub

NoMoreLinks:
On Error GoTo 0
Resume EndRoutine
End Sub

Robin Hammond
www.enhanceddatasystems.com

Robin Hammond said:
Teresa,

This is a very cut down extract from one of a series of complex tracing
routines you can find in my XspandXL add in on my site.

Have a look at the Tracing and Auditing section here:
http://www.enhanceddatasystems.com/ED/Pages/XspandXLAdditional.htm

To trace dependents on another sheet you need to use the following
technique. As I've produced it here, this only tests a single cell. You also
need to watch out that the dependents are not in hidden sheets, since Excel
will not follow the link to something hidden.

Sub Externals()
Dim rngTest as Range
Dim nLinkNumber as Integer
Dim shThisSheet as Worksheet

'show arrows which may or may not have external links
set rngTest = ActiveCell

'excel raises an error if there are no dependents so trap it
On Error Goto NoMoreLinks
rngTest.ShowDependents nLinkNumber = 1
On Error Goto 0

nLinkNumber = 1
Set shThisSheet = ActiveSheet
Do
'if there are no more links excel raises an error
On Error GoTo NoMoreLinks
rngTest.NavigateArrow TowardPrecedent:=False, ArrowNumber:=1,
LinkNumber:=nLinkNumber
On Error GoTo 0

'if you find the original cell there are no externals
If ActiveCell Is rngTest Then Exit Do

If Not ActiveCell.Parent Is shThisSheet Then
'you have found an external dependent
End If
nLinkNumber = nLinkNumber + 1
Loop

EndRoutine:
Exit Sub

NoMoreLinks:
On Error GoTo 0
Resume EndRoutine
End Sub

Robin Hammond
www.enhanceddatasystems.com
Click to expand...
 
Back
Top