VBA code to jump to a cell using reference in current cell

[gmcgough600]

Hi,

Looking for some help, if I have a simple formula like =A1 in cell B1 I want to write some VBA code so that if I select B1 then run a macro it will then select A1. Slightly more complex as what I have in B1 actually refers to another workbook e.g. ='[book1.xlsx]sheet1'!A1 and I want to select A1 in the other workbook (which will be open). Is this possible?

Thanks

 

[DanteAmor]

Put the following code in the events of your sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim rng As Range
  Dim bk As String, sh As String, cl As String
  If Target.CountLarge > 1 Then Exit Sub
  If Target.Address = "$B$1" Then
    bk = Split(Target.Formula, "[")(1)
    bk = Split(bk, "]")(0)
    sh = Split(Target.Formula, "]")(1)
    sh = Split(sh, "!")(0)
    cl = Split(Target.Formula, "!")(1)
    Set rng = Workbooks(bk).Sheets(sh).Range(cl)
    rng.Parent.Activate
    rng.Select
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[gmcgough600]

Put the following code in the events of your sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim rng As Range
  Dim bk As String, sh As String, cl As String
  If Target.CountLarge > 1 Then Exit Sub
  If Target.Address = "$B$1" Then
    bk = Split(Target.Formula, "[")(1)
    bk = Split(bk, "]")(0)
    sh = Split(Target.Formula, "]")(1)
    sh = Split(sh, "!")(0)
    cl = Split(Target.Formula, "!")(1)
    Set rng = Workbooks(bk).Sheets(sh).Range(cl)
    rng.Parent.Activate
    rng.Select
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

Thanks, I'd like the code to work for any cell with a formula similar to B1, not just for B1. Is that possible?

 

[DanteAmor]

How which formulas?

 

[gmcgough600]

Also I only want it to run when I press a button.

 

[gmcgough600]

Any formula in the format ='[book1.xlsx]sheet1'!A1 anywhere on the sheet.

Thanks

 

[DanteAmor]

if I select B1 then run a macro

Also I only want it to run when I press a button.

Which option do you want?

Any formula in the format ='[book1.xlsx]sheet1'!A1 anywhere on the sheet.

Have you tried the code yet?
Which formula doesn't work?