Assigning macros to a hyperlink

[Nishant79]

Hi can anyone help me to assign a macro to a hyperlink????

 

[Gary McMaster]

For any hyperlink on any worksheet:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

'Name of procedure to execute below
MyMacro

End Sub

Or, in a specific worksheet:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

'Name of procedure to execute below
MyMacro

End Sub

If you don't really want it to follow a hyperlink and just execute the macro you can set the hyperlink as a bookmark and use the same cell that contains the hyperlink as the "target".

Hope this helps.

Gary

 

[Nishant79]

Thanks will check it out tomo, at present i am out .........Thanks for help though, but could u tell me how do i make a bookmark ???

 

[Gary McMaster]

1. Right click on the cell that is to contain the hyperlink.
2. Pick hyperlink from the popup menu
3. In the hyperlink dialog box select "Place in this document"
4. Enter the address. Same as cell that contains the link. Can be somewhere else if you like but that may irritate your users.

Gary

 

[Nishant79]

I have copied the code but when i run the macro i get an error now which i have high lighted in bold ...If anyone can help me would be great...

Range("C1").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

 

[Gary McMaster]

Your purpose eludes me in attempting to have one hyperlink follow another hyperlink that appears to be the same target but ...

My original sample will not work as written to execute the latest code you have shown. The original code was intended for the "Follow Hyperlink" event. If the procedure ("MyMacro" shown in the original sample) is supposed to follow another hyperlink, it is causing a recursion.

For example:

A hyperlink is activated, the "Follow Hyperlink" event fires and calls a macro that activates another hyperlink which causes the "Follow Hyperlink" event to fire again before it has even finished processing the first hyperlink that triggered it. It results in the two procedures basically doing nothing but calling each other until system resources are exhausted.

You could try disabling events as shown below. If you can provide a concise description of exactly what you are trying to do maybe someone can offer a better solution.

Gary

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Application.EnableEvents = False

'Name of procedure to execute below
MyMacro

Application.EnableEvents = True


End Sub

 

[Nishant79]

Hey gary thanks for the help but its getting too complicated for me as i am not a programmer so i will solve my query by an easy way i think ....my apologies for the trouble .....thanks for the help and your time