Hey, great! It works. Thank you.
I did not know about the .HasFormula and .Formula properties.
John Wirt


"keepITcool" wrote in message
ft.com...

This should work.

If both procedures are in the SAME module the rngOri variable can be
private, as long as it's declared at module level.

You only need to declare it public if you want to call it:
a.from a procedure in another workbook.
b.from a procedure in another module in the same workbook without
qualifying the module name.

e.g. from a procedure in module2 rngOri will fail if it's not public,
but module1.rngOri will work.



I've reduced the code to the bare minimum but added an error handler
for formulas like =sum(a1:a2) In your original many of the variables
aren't truely needed and I happened to know that Excel can handle
strings like
Range("=[book1.xls]sheet1!a3") without using Mid$ to strip the "="


Option Explicit

Dim rngOri As Range


Sub FindLinkedCell()
Dim rngDst As Range

Set rngOri = Nothing

With ActiveCell
If Not .HasFormula Then
MsgBox "cell has no formula"
Else
On Error Resume Next
Set rngDst = Range(.Formula)
On Error GoTo 0
If rngDst Is Nothing Then
MsgBox "cell is not linked to 1 other cell" & vblf & _
"or destination workbook not open"
Else
Set rngOri = .Cells(1)
Application.Goto rngDst, True
End If
End If
End With
End Sub

Sub ReturnToCell()
If rngOri Is Nothing Then
MsgBox "No cell to return to"
Else
Application.Goto rngOri, True
Set rngOri = Nothing
End If
End Sub





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


John Wirt wrote :

The answer is, no, it doesn't work. The first procedure works. If I
select a linked cell, the procedure chanages to the worksheet with
the destination cell and selects that cell. Colors it dark orange
even.

The Public variable declaration does not seem to work. The first
procedure stores the original range in the Public variable, RAorig.

The second procedure calls that variable and attempts to reference
it. The result of the Set RA= statment is "nothing." RA is set to
nothing.

So something is wrong. John Wirt

Here is the code:
- - - - - - - - - - - - - - - - - - -
Public RAorig As Range

Sub FindLinkedCell()
Dim CellFormula As String
Dim OK As VbMsgBoxResult
Dim WshName As String
Dim RAdest As Range
Dim RAorig As Range

Set RAorig = Selection
WshName = ActiveSheet.Name

CellFormula = RAorig.Formula

Select Case Left(CellFormula, 1)
Case ""
OK = MsgBox("Cell is empty.", vbOKOnly)
Exit Sub
Case "="
Case Else
OK = MsgBox("Cell value is not a link.", vbOKOnly)
Exit Sub
End Select

CellAddress = Mid(CellFormula, 2, Len(CellFormula) - 1)
Set RAdest = Range(CellAddress)
Application.Goto Reference:=RAdest, Scroll:=True
RAdest.Interior.ColorIndex = 40
End Sub

Public Sub ReturntoCell()
Dim RAaddress As String
Dim RA As Range

Set RA = RAorig
Application.Goto Reference:=RAorig

End Sub
- - - - - - - - - - - - - - - - - - - - - -
"Jim Thomlinson" wrote in
message ...
That should work for you... The only thing to look out for is that
it will potentially unload depending how errors are handled...

HTH

"John Wirt" wrote:

I want to store a range in a public variable that lasts as long
as the workbook is open.

Will this declaration in a Module header accomplish this:

Public RA as Range

Sub XXX...
Set RA=Range("A1")
....

The scope of this variable is the project. What is its lifetime?

John Wirt