finding DirectPrecedents

Manually, use Tools|Auditing

For VBA/Excel, please ask.

Kloppa,

Here are 3 VBA procedures to:
a) show all dependents
b) show all precedents
c) remove the arrows

You just need to add three buttons called "Precedents", "Dependents" and "RemoveArrows" and paste the following procedures. You may want to make the error handling more elaborate, but I am sure you get the idea.

Good Luck

garethd


Private Sub Precedents_Click()
On Error Resume Next  ' prevent error when no precedents

Dim r As Range

ActiveCell.ShowPrecedents     ' draw arrrow for first precedent

For Each r In ActiveCell.precedents      ' draw arrrow for secondary precedents
    r.ShowPrecedents
Next r

End Sub

Private Sub Dependents_Click()
On Error Resume Next  ' prevent error when no dependents

Dim r As Range

ActiveCell.ShowDependents  ' draw arrrow for first dependent

For Each r In ActiveCell.dependents   ' draw arrrow for secondary dependents
    r.ShowDependents
Next r

End Sub

Private Sub RemoveArrows_Click()
ActiveSheet.ClearArrows

End Sub

what about a really complex sheet with precedents into 3rd or 4th degree? i'm wondering about that since this q popped up....

:O)Bruintje

gareth, why programming buttons if you can fetch a whole toolbar in View|Toolbars|Customize|Toolbars: Auditing ? And these buttons do increment...

Additionally, ShowDependents does not work for Excel 97, for compatibility use this workaround:
Application.ExecuteExcel4Macro "TRACER.DISPLAY(FALSE,TRUE)"

kloppa, if you state what you are trying to accomplish, perhaps a better solution can be found. I happen to have a macro for cell _ranges_ (plus one to switch the beeping off...)

kloppa, no need to increase the points, feedback would be sufficient.

cri, i edited my question as soon as i got your response
may be it's a better idea to post comments?

Yep, I did not notice it. Editing the question is for typos/grammar only, otherwise it makes the thread very difficult to follow and the experts look like they can not read.

sorry, didn,t know

sorry, didn,t know

Kloppa,

The precedents and dependents are already part of a collection as you can see from the the code I posted earlier:

Dim r As Range

For Each r In ActiveCell.precedents      
  r.ShowPrecedents
Next r

This returns a collection of range values and will pick up all levels (even on other workbooks)

to get the cell locations you can use:

r.address , r.column, r.row

Hope this helps

Garethd

 

Garethd,

i can make your code to get all of the precedents (on every level) of a cell but only on the same (active) worksheet.
i dont know how to get precedents from other worksheets
any suggestions?

thanks for your help

kloppa

Garethd,

i can make your code to get all of the precedents (on every level) of a cell but only on the same (active) worksheet.
i dont know how to get precedents from other worksheets
any suggestions?

thanks for your help

kloppa

Garethd,

i can make your code to get all of the precedents (on every level) of a cell but only on the same (active) worksheet.
i dont know how to get precedents from other worksheets
any suggestions?

thanks for your help

kloppa

dont know why some of my comments appear more then once

kloppa

Sorry Kloppa,

I was inaccurate when I stated that this technique would show precedents on other sheets. It shows a graohical link as I am sure you observed but the address is not returned as part of the collection.

Here is my suggestion (though I don't have time to code it all or test it , I am sure you get the idea):

1. You need an extra loop to control change in worksheet
2. At end of the inside loop (the one I already gave) you check to see if the last formula contained a reference to another sheet. If it does then you activate that sheet and perform the inner loop again.

I can spend more time on the coding later if you need it
Let me know !

Good Luck

GarethD


e.g.



Do       ' Outer Loop

Activecell.ShowPrecedents

For Each r In ActiveCell.Precedents

nextprecedent = r.address    
CellFormula = r.FormulaLocal

next r


If InStr(1, CellFormula, "!") Then
    ' code here to parse out the new sheet name AND new cell address (range) using
    ' CellFormula string and the functions Instr() and mid()  AND THEN
    ' Activate the new sheet and cell like this
 
 Worksheets(NewSheetName).Activate
 ActiveSheet.Range(NewCellAddress).Activate
FoundNewSheet = True
 
End If

loop while FoundNewSheet = true    ' outer Loop

GarethD,

i think i need your help ( if you have some time )

thanks so much

kloppa

thanks everyone.

GarethD,

i appreciate your help

kloppa