Checking cell for Dependents

[ExcelMonkey]

I was hoping that there would be dependent properties that I could test
TRUE or FALSE. It looks as though the examples you have are methods.
Is it possible to test the method to see if it fails. That is if i
you go

cell.dependents.select

and there are not any dependents will the code fail. If so, can I tra
the error and pass this to a TRUE FALSE variable.

Thanks

R

 

[Robin Hammond]

Doing this reliably is not trivial since the dependents property only works
for cells on the same sheet as the original. If you want it to be exhaustive
you also have to use the showdependents method and navigate method, then use
an error trap to see if a dependent exists in another sheet. If you want an
exhaustive suite of dependent, precedent and circularity testing tools, you
could check out my XspandXL add on my site. For the time being, this will
tell you if a range of one or more cells has any internal dependents.

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 Function

Robin Hammond
www.enhanceddatasystems.com