Does a HasDependents = True property exist?

[Dave O]

Does VBA code exist that returns a True when a cell has one or more
dependents?

Thanks

 

[Jim Cone]

You have to roll your own.
There is a HasFormula property for a cell.
There is also a Precedents and a Dependents property that returns a Range object.
So...

Function AreThereKids(ByRef CellPassedIn As Range) As Boolean
Dim objRng As Range
On Error Resume Next
Set objRng = CellPassedIn.Dependents
On Error GoTo 0
AreThereKids = Not objRng Is Nothing
End Function
--
Jim Cone
Portland, Oregon USA
(xl2003 color sorting... http://www.contextures.com/excel-sort-addin.html)



"Dave O" <[email protected]>
wrote in message Does VBA code exist that returns a True when a cell has one or more
dependents?

Thanks

 

[Dave O]

Jim-
Thanks for your note. Not to be picky: is there a way to write that
same code as a routine, rather than a function? Here's my deal: I've
written code (below) to check if a range contains any values (so I can
know whether it is safe to delete). I'd like to incorporate code that
checks each cell to see if that cell has dependents, so I know not to
delete the cell even if it is empty (since deleting the cell causes a
#REF! error in dependent cells).

Your suggestion is excellent- however if I use the function in a
spreadsheet cell, the function itself becomes a dependent of the cell,
and returns a false positive.

Any ideas?

code follows:
Sub WhatsInRange()
Dim rCell As Range
For Each rCell In Selection
If rCell.Value <> "" Then
Range(rCell.Address).Select
End
End If
Next rCell
MsgBox "Nothing found."
End Sub

Thanks again,
Dave O

 

[Jim Cone]

You can call functions/subs from functions or subs.
'--
Sub WhatsInRange_R1()
Dim rCell As Range
For Each rCell In Selection.Cells
If IsEmpty(rCell) Then
If AreThereKids(rCell) = False Then
Range(rCell.Address).Select
Exit Sub
End If
End If
Next 'rCell
MsgBox "Nothing found."
End Sub

Function AreThereKids(ByRef CellPassedIn As Range) As Boolean
Dim objRng As Range
On Error Resume Next
Set objRng = CellPassedIn.Dependents
On Error GoTo 0
AreThereKids = Not objRng Is Nothing
End Function
--
Jim Cone
Portland, Oregon USA
(free excel add-in to remove excess Styles or Cond. Formats... http://excelusergroup.org/media/p/4861.aspx )





"Dave O" <[email protected]>
wrote in message Jim-
Thanks for your note. Not to be picky: is there a way to write that
same code as a routine, rather than a function? Here's my deal: I've
written code (below) to check if a range contains any values (so I can
know whether it is safe to delete). I'd like to incorporate code that
checks each cell to see if that cell has dependents, so I know not to
delete the cell even if it is empty (since deleting the cell causes a
#REF! error in dependent cells).

Your suggestion is excellent- however if I use the function in a
spreadsheet cell, the function itself becomes a dependent of the cell,
and returns a false positive.

Any ideas?

code follows:
Sub WhatsInRange()
Dim rCell As Range
For Each rCell In Selection
If rCell.Value <> "" Then
Range(rCell.Address).Select
End
End If
Next rCell
MsgBox "Nothing found."
End Sub

Thanks again,
Dave O

 

[Dave O]

Your kung fu is vastly superior!

Thanks for your help~

Dave O