HYPER LINK COMMAND BUTTON

[charly1]

Hi

I have a formule entered into a cell with a dynamic hyperlink copied below:

=HYPERLINK("#"&"מפתח!E"&(XMATCH(B4,BOOK)+12),"Hello")

My question is, is there a way I can insert a a command button into my sheet to run the hyperlink contained in the cell?

Thanks so much.

 

[Georgiboy]

Can you explain 'run the hyperlink contained in the cell' a bit more?

Does clicking on the link not run the hyperlink?

 

[charly1]

Can you explain 'run the hyperlink contained in the cell' a bit more?

Does clicking on the link not run the hyperlink?

It does actually work when i click on the link, its just that for my dashboard i would like a command button to act as though ive clicked on that cell link.
Thanks so much

 

[Georgiboy]

I see, perhaps the below will work for you:

Sub test()
    Dim ws As Worksheet
    Dim rCell As Range
    Dim re As Object
    
    Set ws = Sheets("Sheet1")
    Set rCell = ws.Range("A1")
    
    Set re = CreateObject("VBScript.RegExp")
    re.Pattern = "^=HYPERLINK\(""([^""]+)"""
    
    If re.test(rCell.Formula) Then
        ThisWorkbook.FollowHyperlink re.Execute(rCell.Formula)(0).SubMatches(0)
    End If
End Sub

 

[charly1]

I see, perhaps the below will work for you:

Sub test()
    Dim ws As Worksheet
    Dim rCell As Range
    Dim re As Object
   
    Set ws = Sheets("Sheet1")
    Set rCell = ws.Range("A1")
   
    Set re = CreateObject("VBScript.RegExp")
    re.Pattern = "^=HYPERLINK\(""([^""]+)"""
   
    If re.test(rCell.Formula) Then
        ThisWorkbook.FollowHyperlink re.Execute(rCell.Formula)(0).SubMatches(0)
    End If
End Sub

I placed this code into the command button, but it doesn't seem to work.

I am new to excel though, so the fault is likely mine!

I also tried to change the A1 reference in your code to the cell address of the formula but it still didn't help

Thanks so much for your patience and time.

 

[Peter_SSs]

I also tried to change the A1 reference in your code to the cell address of the formula

What is that cell address?
You will have to do the same thing with this code but give it a try.
Not sure if you will want the Scroll:=True?

Sub Testing()
  Dim s As String, f As String
 
  f = Replace(Range("A1").Formula, "'", "")
  s = Evaluate(Left(f, InStrRev(f, ",") - 1) & ")")
  Application.Goto Reference:=Sheets(Mid(Split(s, "!")(0), 2)).Range(Split(s, "!")(1)), Scroll:=True
End Sub

 

[Georgiboy]

When placing the code into a command button, only place the code between the Sub & End Sub lines, do not include those lines. You may have to amend the sheet name also.

The code will look like the below:

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim rCell As Range
    Dim re As Object
    
    Set ws = Sheets("Sheet1")
    Set rCell = ws.Range("A1")
    
    Set re = CreateObject("VBScript.RegExp")
    re.Pattern = "^=HYPERLINK\(""([^""]+)"""
    
    If re.test(rCell.Formula) Then
        ThisWorkbook.FollowHyperlink re.Execute(rCell.Formula)(0).SubMatches(0)
    End If
End Sub

 

[charly1]

What is that cell address?
You will have to do the same thing with this code but give it a try.
Not sure if you will want the Scroll:=True?

Sub Testing()
  Dim s As String, f As String
 
  f = Replace(Range("A1").Formula, "'", "")
  s = Evaluate(Left(f, InStrRev(f, ",") - 1) & ")")
  Application.Goto Reference:=Sheets(Mid(Split(s, "!")(0), 2)).Range(Split(s, "!")(1)), Scroll:=True
End Sub

Thanks a mil! It worked like a charm!

 

[Peter_SSs]

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

@Georgiboy
I couldn't get your code to work in my test workbook either.

 

[Georgiboy]

How strange, working my end. Oh well a solution has been found, that's the main thing.