VBA - Dynamic Macro to select a cell referenced by another

[rsulliva]

I would like a macro that will jump to the referenced cell within the formula of the active cell. The active cell and referenced cell could be anywhere within the current workbook. In other words, if Sheet 1 Cell A1 has the reference formula ='Sheet 2'!B1 in it, and assuming I have activated (selected) Cell A1 of Sheet 1, when run the macro will take reference the precedent in this cell to take me to Sheet 2, B1 and select this cell. This will ultimately be a piece of a much larger macro. Any suggestions? Thanks

 

[myall_blues]

Couldn’t you just use the HYPERLINK formula?

=HYPERLINK("#'Sheet2'!B1","Sheet2-B1")

 

[kvsrinivasamurthy]

Use worksheet event

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Dim M
M = Split(Replace(Replace(Target.Formula, "=", ""), "'", ""), "!")
Sheets(M(0)).Activate
ActiveSheet.Range(M(1)).Select
End If
End Sub

How to use worksheet event the code
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Paste the code
Close the VB window.
Save the file as .xlsm

 

[rsulliva]

Hey guys, Thanks for your suggestions. However, I am wanting to be able to run this code when I am on any given cell within any given workbook I have open at the time. Therefore, it will have to be run from the personal macro workbook and cannot be cell, sheet or workbook specific. Any other suggestions are welcomed.

(It will be similar to going into the Excel Options/Advanced/deselecting Allow editing directly in cells. This enables you to double click a cell with a reference and it automatically takes you directly to the referenced cell.) But I need this function to be incorporated into more code. So, I need to know how to code this functionality in VBA without the double click of the cell and setting the Excel Options.

 

[Peter_SSs]

1. Are all the formulas that might be in whatever the ActiveCell is be a simple link to another cell like your example or might the formula be, for example =Sheet2!B1*15 or ="abc"&K4&"def"?
   
   
2. If they are not all simple direct links to another cell, what is supposed to happen if the active cell formula references more that one cell? For example, this one references 3 cells on 3 different sheets.
   =D15+'Sheet 2'!B1+'Sheet 3'!X5

 

[rsulliva]

Hey Peter,
Question #1 - Answer - is yes they should all be simple links.
Questions #2 - Answer - if by chance it does have more than one link in the cell, just going to the first link would be perfect.
Thanks

 

[Peter_SSs]

Give this a try

Sub Test()
  Application.DoubleClick
End Sub

 

[rsulliva]

Hey Peter_SSs

This code works great. How simple it is!

Thank you

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.