hyperlinking to a macro?

[MWE]

I wish to build a hyperlink from a specific cell to a macro. I do not want to start the macro from a command button, menu item, text box or some other obvious method.

The WIP spreadsheet automatically build hyperlinks from a "file list" to target files in appropriate folders. In 95% of the cases, the # of files in the folder is 1 and the hyperlink is to the single file in the target folder. But in 5% of the cases, the folder contains multiple files. So the "hyperlink macro" fetches the names of the files in the target folder, and displays them in a listbox on a form and lets the user select the file he wishes to open. So, is there a way to build a hyperlink that executes a macro?

BTW, I have already solved the problem the "other way", i.e., the user selects the relevant cell and clicks a button to open the file. The macro assigned to the button checks to see if there is 1 or multiple files in the folder. If 1, the file is opened with a followhyperlink. If > 1, the listbox form pops up ... This is OK, but I would like to eliminate the two step process required of the user.

Thanks

[Bob Phillips]

Have a look at the FollowHyperlink event, you can trap it just as it says.

[Zack Barresse]

Why not just use a SelectionChange event?

[MWE]

Have a look at the FollowHyperlink event, you can trap it just as it says.thanks for the reply. I do not see how that would work. The FollowHyperlink event assumes that some VBA macro is already executing. What I want to do is CREATE a hyperlink from a given cell to a given macro. If I could figure out how to do that manually, I could probably figure out how to do it programatically.

[MWE]

Why not just use a SelectionChange event?thanks for the reply. I have explored that approach and was able to get it to work. I have several workarounds, all of which are OK (for Excel), but I still would like to do it the way I originally outlined so I can generalize it to any application.

[Zack Barresse]

You can do it just the way Bob described it. Assuming you have two workbooks, Test1.xls and Test2.xls. In the first workbook you have a routine named Sub1 and you have a hyperlink to Test1 located in Test2. In the sheet code for the hyperlink on Test2.xls, this code works...

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Application.Run Target.Address & "!Sub1"
End Sub

The Target is the hyperlink address, so as long as you pointed it to the workbook you'd be fine. It works for me.

[MWE]

You can do it just the way Bob described it. Assuming you have two workbooks, Test1.xls and Test2.xls. In the first workbook you have a routine named Sub1 and you have a hyperlink to Test1 located in Test2. In the sheet code for the hyperlink on Test2.xls, this code works...

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Application.Run Target.Address & "!Sub1"
End Sub
The Target is the hyperlink address, so as long as you pointed it to the workbook you'd be fine. It works for me.this does not work ... well, rather, it does not do what I want so I must be describing the problem poorly.

Assume a single spreadsheet, A.xls, and a single worksheet in A named "B". In Col 1 of B are names of external files. In most cases, the desired operation is to simply have the target external file open when the user clicks on the filename. For that, a simple hyperlink will work.

However, in some limited number of cases, the filename is not really for a file, but has some other purpose and when the user clicks on the filename for those special cases, I want a macro to execute and I do not want the application to try to hyperlink to the bogus Target. By some magic, I know which of these filenames are a real file and which are just placeholders.

The approach suggested above would work quite well if I could control whether or not the hyperlink actually executes. I could test the value of Target and if it is for a real file, let the hyperlinnk execute; otherwise the procedure does something else. The problem is that the Worksheet_FollowHyperlink procedure is called AFTER the hyperlink executes. I tried hyperlinking to a bogus file to see if I could at least "do something" after the hyperlink execution failed. Unfortunately, the Worksheet_FollowHyperlink procedure executes only if the hyperlink execution is successful.

Any further thoughts?

[mdmackillop]

Try this. In my example "Test4" is in A4 and hyperlinks to itself.
Regards
MD

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
If Right(UCase(Target.Address), 3) <> "XLS" Then
Application.Run Selection.Text
End If
End Sub

Sub Test4()
MsgBox "Hi!"
End Sub