Hi

have a question re Precedents in Excel. When I click a cell containg some
references to other worksheets a small icon shows up next to the cell. When
clicked it shows a Go to window with references (paths) to precedent cells
from other worksheets. I want to get that list of references using VBA. When
I record a macro it looks like this:

Sub Makro3()

Selection.ShowPrecedents
'I clicked the third position from the list
ActiveCell.NavigateArrow TowardPrecedent:=True, ArrowNumber:=1,
LinkNumber _
:=3
ActiveSheet.ClearArrows
End Sub

How can I get the reference that appears in this macro as LinkNumber:=3. I
want to use this in some other place in my code so I want to assign it to a
variable of string type.

Kind regards
Igor

The NavigateArrow method returns a range object. Set up a counter for
LinkNumber and build up a collection or array of the range objects or range
addresses, whatever you want. With each range object check to see whether
the range is on the same sheet as your source cell. When you get one that
is (likely the source cell itself oddly enough), you're done. I've found
that off-sheet references always come back first. Although I haven't found
any documentation that says this I've successfully relied on this for many
years.

--
Jim
"IgorM" wrote in message
...
| Hi
|
| have a question re Precedents in Excel. When I click a cell containg some
| references to other worksheets a small icon shows up next to the cell.
When
| clicked it shows a Go to window with references (paths) to precedent cells
| from other worksheets. I want to get that list of references using VBA.
When
| I record a macro it looks like this:
|
| Sub Makro3()
|
| Selection.ShowPrecedents
| 'I clicked the third position from the list
| ActiveCell.NavigateArrow TowardPrecedent:=True, ArrowNumber:=1,
| LinkNumber _
| :=3
| ActiveSheet.ClearArrows
| End Sub
|
| How can I get the reference that appears in this macro as LinkNumber:=3. I
| want to use this in some other place in my code so I want to assign it to
a
| variable of string type.
|
| Kind regards
| Igor
|
|

Is it possible to check the number of LinkNumber and ArrowNumber ?

"Jim Rech" wrote in message
...
The NavigateArrow method returns a range object. Set up a counter for
LinkNumber and build up a collection or array of the range objects or
range
addresses, whatever you want. With each range object check to see whether
the range is on the same sheet as your source cell. When you get one that
is (likely the source cell itself oddly enough), you're done. I've found
that off-sheet references always come back first. Although I haven't found
any documentation that says this I've successfully relied on this for many
years.

--
Jim
"IgorM" wrote in message
...
| Hi
|
| have a question re Precedents in Excel. When I click a cell containg
some
| references to other worksheets a small icon shows up next to the cell.
When
| clicked it shows a Go to window with references (paths) to precedent
cells
| from other worksheets. I want to get that list of references using VBA.
When
| I record a macro it looks like this:
|
| Sub Makro3()
|
| Selection.ShowPrecedents
| 'I clicked the third position from the list
| ActiveCell.NavigateArrow TowardPrecedent:=True, ArrowNumber:=1,
| LinkNumber _
| :=3
| ActiveSheet.ClearArrows
| End Sub
|
| How can I get the reference that appears in this macro as LinkNumber:=3.
I
| want to use this in some other place in my code so I want to assign it
to
a
| variable of string type.
|
| Kind regards
| Igor
|
|