Checking cell for Dependents

[ExcelMonkey]

Better question for you Robin. I am using the code below to call you
function on a cell in that I call StartCell. I have created dependent
in the same sheet and several other sheets in my workbook. The cod
below will actually select each depent cell. However you will notic
that I am using a For Each loop (1 to 1000000). I could not figure ou
how to use a For Each loop with NavigateArrows. I also assumed that
could limit the loop by putting a On Error stmt in so that when i
tried to navigate an error that does not exist it would create an erro
and exit the loop. This is not working.

Now this is actually overkill, because all I really want to do i
identify WHEN a navigation arrow takes me to a sheet other than th
sheet that the cell is in. How do I trap the event that takes me t
another sheet? I don't really care where it goes, but I do care IF i
goes. Thanks

Sub Thing()
Dim Cell As Range
Dim HasDep As Boolean
Dim CountDep As Integer
Dim X As Double

Set Cell = Range("StartCell")

HasDep = HasInternalDependents(Cell)

If HasDep = True Then
Cell.ShowDependents
End If

For X = 1 To 1000000
On Error Resume Next
Cell.NavigateArrow True, 1
ActiveCell.NavigateArrow TowardPrecedent:=False, ArrowNumber:=X, _
LinkNumber:=1
On Error GoTo 0
Next X

End Sub

Public Function HasInternalDependents(rngTest As Range) As Boolean
Dim rngCell As Range
Dim rngDep As Range

For Each rngCell In rngTest
Set rngDep = Nothing
On Error Resume Next
Set rngDep = rngCell.Dependents
On Error GoTo 0
If Not rngDep Is Nothing Then
HasInternalDependents = True
Exit Function
End If
Next rngCell
End Functio

 

[keepITcool]

ExcelMonkey..

it was an interesting experiment..BUT...

Following works nicely for a few 'external dependendents'.
If you have more then say 500 or so, the .NavigateArrow takes
progressively longer to execute making this approach fairly limited in
it's application.

NOTE for external dependents you'll have to increment the the THIRD
parameter not the 2nd. I've built in a escape hatch to exit on more than
1024 deps.


Function ExternalDependents(SrcRange As Range) As Collection

Dim DstRange As Range, Externals As New Collection, n&

If TypeOf Application.Caller Is Range Then GoTo theExit
If SrcRange.Cells.Count > 1 Then GoTo theExit

On Error Resume Next
Application.ScreenUpdating = False

With SrcRange
If Not .DirectDependents Is Nothing Then
.ShowDependents True
.ShowDependents False
'Escape if there are too many...
Set DstRange = .NavigateArrow(False, 1, 1025)
If Err = 0 Then
Externals.Add CVErr(xlErrValue)
GoTo theExit
End If
n = 1
Do
Set DstRange = .NavigateArrow(False, 1, n)
If Err <> 0 Then Exit Do
Externals.Add DstRange
n = n + 1
Loop While n <= 1024
End If
End With
Stop
theExit:
Application.ScreenUpdating = True
Set ExternalDependents = Externals
End Function