VBA Call Macro From Hyperlink Not Working

[Barklie]

Hello,

I am trying to create a column of hyperlinks that either 1) directs to a customer's workbook, or 2) for new customers, creates a new customer workbook and changes the active cell to make it link to their new workbook. I have a macro that completes this task, called NewCustomer, but am having trouble calling it from a link. I am currently working with code that I found online (see below), but cannot make it work. Perhaps I am not putting the link into the cell correctly, or the code in the correct place (private subs go into ThisWorkbook right?). Any help would be greatly appreciated.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Range.Address = "$A$1" Then
        MsgBox "it's the hyperlink in A1"
        Call NewCustomer
    End If
End Sub

 

[mrmmickle1]

This looks like the proper syntax to me. I did a test using dummy data and it worked for me. Are you seeing the msgbox pop up or do you not see this at all?

Is it possible that you inadvertently disabled events? Try accessing the Immediate window in the VBE (Ctrl + G) then execute this line of code in the window.

Application.EnableEvents = True

Then try again.

 

[Barklie]

Hey mrmickle,

I ran your code in the Immediate window and am still having trouble. The macro works fine when executing it from VBE. It only doesn't work when clicking the link.

Thanks for the reply. What am I supposed to have in the cell field? Right now in Cell A1 I have the Text to Display as "Link" and the Hyperlink Address as "A1". Pressing this should execute the macro?

 

[mrmmickle1]

Try pasting the code to the worksheet module that the the Hyperlink exists on.... i.e. if the Hyperlink is in Sheet1 then paste code to the Sheet1 code module.

Right now in Cell A1 I have the Text to Display as "Link" and the Hyperlink Address as "A1". Pressing this should execute the macro?

I created a similar setup to test. Yes, this should execute the macro.

The ThisWorkbook Event has different syntax. (Sorry, I didn't pick up on that the first time....)

[COLOR=#0000ff]Private Sub [/COLOR]Workbook_SheetFollowHyperlink([COLOR=#0000ff]ByVal [/COLOR]Sh[COLOR=#0000ff] As [/COLOR]Object, [COLOR=#0000ff]ByVal[/COLOR] Target [COLOR=#0000ff]As [/COLOR]Hyperlink)
[COLOR=#0000ff]End Sub[/COLOR]

 

[Barklie]

Thanks again for your help. I'm still getting the same error. When I click the link, it returns, "Cannot open the specified file". This happens when I use your workbook level code or the Sheet level code that I got from the web. It doesn't seem like its even getting to any back-end stuff.

Thanks,
Barklie

 

[mrmmickle1]

Sounds like the event is working now, but the code is not..... What code do you have trying to open files?

Does it involve a shared drive? If so this can be tricky seeing as different users will have their drives mapped to different letters.

For example at my company I have a drive called HR Data Requests Mapped to Z:\ while my co -worker maps it to X:\ .... The only way to get around this is to use an absolute path in the link.

So rather then link to a file like this:

Z:\Assistant\Business Queries\2016 Q2\MyFile.xlsx

you would have to link to the file like this using an absolute path:

ns-linyvfs02\HR DataRequests\Assistant\Business Queries\2016 Q2\MyFile.xlsx