Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dependents on another worksheet
Hi,
The dependents property of a Range object only returns dependen cells on the same worksheet. How do I collect dependent cells on othe worksheets? And what is the difference between Dependents and DirectDependents? Thanks. Teres -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dependents on another worksheet
Teresa,
This is a very cut down extract from one of a series of complex tracing routines you can find in my XspandXL add in on my site. Have a look at the Tracing and Auditing section he http://www.enhanceddatasystems.com/E...Additional.htm To trace dependents on another sheet you need to use the following technique. As I've produced it here, this only tests a single cell. You also need to watch out that the dependents are not in hidden sheets, since Excel will not follow the link to something hidden. Sub Externals() Dim rngTest as Range Dim nLinkNumber as Integer Dim shThisSheet as Worksheet 'show arrows which may or may not have external links set rngTest = ActiveCell 'excel raises an error if there are no dependents so trap it On Error Goto NoMoreLinks rngTest.ShowDependents nLinkNumber = 1 On Error Goto 0 nLinkNumber = 1 Set shThisSheet = ActiveSheet Do 'if there are no more links excel raises an error On Error GoTo NoMoreLinks rngTest.NavigateArrow TowardPrecedent:=False, ArrowNumber:=1, LinkNumber:=nLinkNumber On Error GoTo 0 'if you find the original cell there are no externals If ActiveCell Is rngTest Then Exit Do If Not ActiveCell.Parent Is shThisSheet Then 'you have found an external dependent End If nLinkNumber = nLinkNumber + 1 Loop EndRoutine: Exit Sub NoMoreLinks: On Error GoTo 0 Resume EndRoutine End Sub Robin Hammond www.enhanceddatasystems.com "tyeung4 " wrote in message ... Hi, The dependents property of a Range object only returns dependent cells on the same worksheet. How do I collect dependent cells on other worksheets? And what is the difference between Dependents and DirectDependents? Thanks. Teresa --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dependents on another worksheet
Addendum:
This gets rid of the arrows after the trace. Sub Externals() Dim rngTest As Range Dim nLinkNumber As Integer Dim shThisSheet As Worksheet 'show arrows which may or may not have external links Set rngTest = ActiveCell 'excel raises an error if there are no dependents so trap it On Error GoTo NoMoreLinks rngTest.ShowDependents nLinkNumber = 1 On Error GoTo 0 nLinkNumber = 1 Set shThisSheet = ActiveSheet Do 'if there are no more links excel raises an error On Error GoTo NoMoreLinks rngTest.NavigateArrow TowardPrecedent:=False, ArrowNumber:=1, LinkNumber:=nLinkNumber On Error GoTo 0 'if you find the original cell there are no externals If ActiveCell Is rngTest Then Exit Do If Not ActiveCell.Parent Is shThisSheet Then MsgBox "this cell has external dependents" End If nLinkNumber = nLinkNumber + 1 Loop EndRoutine: shThisSheet.Activate rngTest.Select ActiveSheet.ClearArrows Exit Sub NoMoreLinks: On Error GoTo 0 Resume EndRoutine End Sub Robin Hammond www.enhanceddatasystems.com "Robin Hammond" wrote in message ... Teresa, This is a very cut down extract from one of a series of complex tracing routines you can find in my XspandXL add in on my site. Have a look at the Tracing and Auditing section he http://www.enhanceddatasystems.com/E...Additional.htm To trace dependents on another sheet you need to use the following technique. As I've produced it here, this only tests a single cell. You also need to watch out that the dependents are not in hidden sheets, since Excel will not follow the link to something hidden. Sub Externals() Dim rngTest as Range Dim nLinkNumber as Integer Dim shThisSheet as Worksheet 'show arrows which may or may not have external links set rngTest = ActiveCell 'excel raises an error if there are no dependents so trap it On Error Goto NoMoreLinks rngTest.ShowDependents nLinkNumber = 1 On Error Goto 0 nLinkNumber = 1 Set shThisSheet = ActiveSheet Do 'if there are no more links excel raises an error On Error GoTo NoMoreLinks rngTest.NavigateArrow TowardPrecedent:=False, ArrowNumber:=1, LinkNumber:=nLinkNumber On Error GoTo 0 'if you find the original cell there are no externals If ActiveCell Is rngTest Then Exit Do If Not ActiveCell.Parent Is shThisSheet Then 'you have found an external dependent End If nLinkNumber = nLinkNumber + 1 Loop EndRoutine: Exit Sub NoMoreLinks: On Error GoTo 0 Resume EndRoutine End Sub Robin Hammond www.enhanceddatasystems.com "tyeung4 " wrote in message ... Hi, The dependents property of a Range object only returns dependent cells on the same worksheet. How do I collect dependent cells on other worksheets? And what is the difference between Dependents and DirectDependents? Thanks. Teresa --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UDF FOR DEPENDENTS | Excel Discussion (Misc queries) | |||
trace all dependents | Excel Discussion (Misc queries) | |||
trace dependents | Excel Worksheet Functions | |||
how to get address of dependents | Excel Worksheet Functions | |||
trace dependents | Excel Worksheet Functions |