Run Macro Or Hide Sheet Using Formula?

[t0ny84]

Hi,
I was wondering if anyone knows if it's possible and if so how to:

1) Run a macro using a formula in a cell?
2) Using Hyperlink to call macro?
3) Hide worksheet using formula?

Thanks in advance!

t0ny84

 

[t0ny84]

Or another thought, would creating a macro then creating a function to call the macro work?

 

[JoeMo]

Hi,
I was wondering if anyone knows if it's possible and if so how to:

1) Run a macro using a formula in a cell?
2) Using Hyperlink to call macro?
3) Hide worksheet using formula?

Thanks in advance!

t0ny84

2) is possible. 1) and 3) - don't think so.

 

[t0ny84]

2) is possible. 1) and 3) - don't think so.

Thanks JoeMo,
Do you know of a working formula using HYPERLINK?
Thanks,
t0ny84

 

[JoeMo]

Thanks JoeMo,
Do you know of a working formula using HYPERLINK?
Thanks,
t0ny84

No formula necessary. Just put a hyperlink on a worksheet somewhere. In the example below its in cell A1.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
'put this event procedure in the sheet code for the sheet your link is in
'make sure the hyperlink is in A1 (change to suit) of that sheet
If Target.Parent.Address = "$A$1" Then
    Call Hello   'substitute your macro in a standard module here
End If
End Sub
Sub Hello()
'this can be in a standard module or appended to the event module above
MsgBox "HELLO"
End Sub

 

[t0ny84]

No formula necessary. Just put a hyperlink on a worksheet somewhere. In the example below its in cell A1.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
'put this event procedure in the sheet code for the sheet your link is in
'make sure the hyperlink is in A1 (change to suit) of that sheet
If Target.Parent.Address = "$A$1" Then
    Call Hello   'substitute your macro in a standard module here
End If
End Sub
Sub Hello()
'this can be in a standard module or appended to the event module above
MsgBox "HELLO"
End Sub

Hi JoeMo,
Thanks so much it works a treat!
One more question for you, is there a way to only have this once in a workbook either under the ThisWorkbook or on a Module using ActiveSheet or something similar? Will be using this across a number of sheets in the workbook and wanting to keep everything tidy.

Thanks again!
t0ny84

 

[JoeMo]

Hi JoeMo,
Thanks so much it works a treat!
One more question for you, is there a way to only have this once in a workbook either under the ThisWorkbook or on a Module using ActiveSheet or something similar? Will be using this across a number of sheets in the workbook and wanting to keep everything tidy.

Thanks again!
t0ny84

You are welcome - thanks for the reply.

You can adapt this for any sheet by using a Workbook_SheetFollowHyperlink in Thisworkbook. Put the Hello routine in a standard module.

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
If Target.Parent.Address = "$A$1" Then
    Call Hello   'substitute your macro in a standard module here
End If
End Sub

 

[t0ny84]

You are welcome - thanks for the reply.

You can adapt this for any sheet by using a Workbook_SheetFollowHyperlink in Thisworkbook. Put the Hello routine in a standard module.

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
If Target.Parent.Address = "$A$1" Then
    Call Hello   'substitute your macro in a standard module here
End If
End Sub

Hey JoeMo do you know a way of getting this to work with=HYPERLINK function urls?

 

[JoeMo]

Hey JoeMo do you know a way of getting this to work with=HYPERLINK function urls?

No, there's no FollowHyperlink event associated with the HYPERLINK worksheet function that I'm aware of.

 

[t0ny84]

No, there's no FollowHyperlink event associated with the HYPERLINK worksheet function that I'm aware of.

All good thanks so much for your help JoeMo. I was also wondering do you know of a way to use the FollowHyperlink VBA with a range of columns or cells?

It should work with the code below but do to it for each cell will get time consuming.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Range.Address = "$B$6" Then
        Call Macro1
        End If
         If Target.Range.Address = "$B$8" Then
        Call Macro2
         End If
End Sub