Hyperlink to run code

[Martin]

Hi,

Is there a way to create a hyperlink to run code?

I want to do this as there are lots of hyperlinks on my sheet and I want to
continue this theme rather than add buttons.

Thank you,

Martin

 

[Gary''s Student]

You can use the following worksheet event code:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
MsgBox (Target.Address)
End Sub

The code goes in the worksheet code area, not a standard module.

 

[Martin]

Hi,

i get a message saying target required. I have this code:


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
MsgBox (E3.Hide2002)
End Sub

E3 is the cell and as you say I have the code in the worksheet area and
Hide2002 is a macro I have written. Is that written as you would expect?

 

[Gary''s Student]

Almost there!

Replace:
MsgBox (E3.Hide2002)
with:
Call Hide2002

 

[Martin]

Sorry, one last question! If I had more than one hyperlink is there a way to
state which hyperlink calls which function? Something like:

if select.cell.E3 then
Call Hide2002
end if

 

[Gary''s Student]

Yes:

If your hyperlink is in cell C5, for example, then:

ActiveCell.Address would reveal it:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
MsgBox (ActiveCell.Address)
End Sub

So if you have many hyperlinks, at least you know which cell got clicked!

 

[Martin]

Thank you for your persistence with me!

Works a treat.

Martin

 

[Gary''s Student]

Glad to help!
--
Gary''s Student - gsnu200809

Thank you for your persistence with me!

Works a treat.

Martin