Checking cell for Dependents

[ExcelMonkey]

I actually want to be able to test for dependents and precedents o
other sheets. effectively my models have inputs like all models.
want to be able to test to see which cells are inputs in the entir
spreadsheets. I will eventually past the cell address of these onto
summary page as a hyperlink. I have the paste as hyperlink piec
figures out but cannot seem to figure how to identify if in fact a cel
is an input.

So yes, it will be deemed an input if it does not have precedents an
of course it has to have dependents. AlL i need is a boolea
true/false and I am on my way. But I will need it to go beyond th
sheet that the cell is in.

Th

 

[keepITcool]

ExcelMonkey!

... following would work (on my limited test)

it will miss empty cells that have dependents but ONLY if they
are below or to the right of the usedrange.

If a cell is has NO local dependents but has MANY external dependents
you'll note the .NavigateArrow will slow things down. (in some case
bring it to a virtual standstill)


I do test for cells which DO have formulas but DONT use cell references.
(input like = 3*5)

Formulas using named ranges on other sheets(thus have an empty
PRECEDENTS object) may not be picked up correctly.

Adapt to what you actually want to do with the input cells.
(my code will set them to Unlocked and make m RED)


HTH and hope it's enough, cause I dont really want to continue this.
Cheerz!
Jurgen


Option Explicit

Sub UnlockInputCells()

Dim wks As Worksheet, rStart As Range, rCell As Range, n As Long

Set rStart = ActiveCell
Application.ScreenUpdating = False
For Each wks In ActiveWorkbook.Worksheets
With wks
.Unprotect
If .ProtectContents Then
MsgBox wks.Name & " is protected with a pw."
Exit Sub
End If
.Cells.Locked = True
.Cells.Interior.ColorIndex = xlNone
.ClearArrows
For Each rCell In .Range("a1", .UsedRange(.UsedRange.Count))
n = n + 1
If n Mod 100 = 1 Then
Application.StatusBar = rCell.Address(external:=True)
End If
If HasNoFormula(rCell) Then
If HasDependents(rCell) Then
rCell.Locked = False
rCell.Interior.ColorIndex = 3
End If
End If
Next
.EnableSelection = xlUnlockedCells
'.Protect vbNullString, True, True
End With
Next
rStart.Worksheet.Activate
rStart.Activate
Application.ScreenUpdating = True
Application.StatusBar = False

End Sub


Function HasNoFormula(rCell As Range) As Boolean
Dim l As Long
On Error Resume Next
With rCell
If Len(.Formula) = 0 Then
HasNoFormula = True
Else
If .HasFormula = True Then
If .Formula = .FormulaR1C1 Then
l = .Precedents.Count
If l > 0 Then HasNoFormula = True
End If
Else
HasNoFormula = True
End If
End If
End With
End Function

Function HasDependents(rCell As Range) As Boolean
Dim rTest As Range
On Error Resume Next
With rCell
If .DirectDependents Is Nothing Then
.ShowDependents
Set rTest = .NavigateArrow(0, 1, 1)

If rTest.Address(external:=True) <> rCell.Address(external:=True)
Then
HasDependents = True
.Worksheet.ClearArrows
End If
Else
HasDependents = True
End If
End With
End Function