Dependents problem

[Bony Pony]

Hi all,
I have an input cell that is data validated. It is the only cell currently
selected.

In VBA immediate (or in a module) if I check for

?selection.dependents.count it tells me the (1004) No cells were found.

yet when I enter selection.showdependents it shows the dependency "button"
and arrow.

If I look at selection in the watch window,
Dependents - (No cells were found)
Directdependents - (No cells were found)

Please can anybody shed some light on this?

Kind regards,
Bony

 

[Jan Karel Pieterse]

Hi Bony,

yet when I enter selection.showdependents it shows the dependency "button"
and arrow.

If I look at selection in the watch window,
Dependents - (No cells were found)
Directdependents - (No cells were found)

Please can anybody shed some light on this?

The Dependents collection only shows dependents on the same worksheet. The
ShowDependents method shows the arrows pointing to other worksheets too.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

 

[Bony Pony]

OMG! A reply from the co-author of namemanager!! What a brilliant piece of
work that is.

Thanks for the reply Jan Karel.

Is there a way to "know" a cell's dependents?

Thanks again!

Kind regards,
Bony

 

[Jan Karel Pieterse]

Hi Bony,

OMG! A reply from the co-author of namemanager!! What a brilliant piece of
work that is.
)

Is there a way to "know" a cell's dependents?

You can use the navigateArrow method to find them out. Code like this:

Sub Demo2()
Dim oRng As Range
Dim sForm As String
Dim lLink As Long
Dim lArrow As Long
sForm = ActiveCell.Formula & vbNewLine
Set oRng = ActiveCell
oRng.ShowPrecedents
On Error Resume Next
For lArrow = 1 To ActiveSheet.Shapes.Count
For lLink = 1 To 1000
Err.Clear
Application.Goto oRng
oRng.NavigateArrow True, lArrow, lLink
If Err.Number = 0 And oRng.Address(external:=True) <>
Selection.Address(external:=True) Then
If oRng.Parent.Name = ActiveCell.Parent.Name Then
sForm = sForm & vbNewLine & Selection.Address(False, False,
, False)
Else
sForm = sForm & vbNewLine & Selection.Address(False, False,
, True)
End If
Else
Exit For
End If
Next
Next
MsgBox sForm
End Sub

My ReftreeAnalyser (not free, but there is a free demo) does a decent job at
finding them:

www.jkp-ads.com/reftreeanalyser.asp

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

 

[Bony Pony]

Jan Karel many thanks again!

Actually the clue in your code was the activesheet.shapes.count

Simply - if the activesheet.shapes.count didn't change then there are no
dependents / precedents. Sometimes these answers are SO obvious!

Many thanks again.

Kind regards,
Bony