VBA Call Macro From Hyperlink Not Working

Barklie

Board Regular
Joined
Jul 4, 2013
Messages
76
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.

Code:
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,456
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.

Code:
Application.EnableEvents = True

Then try again.
 

Barklie

Board Regular
Joined
Jul 4, 2013
Messages
76
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

Well-known Member
Joined
May 11, 2012
Messages
2,456
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....)
Code:
[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]
 
Last edited:

Barklie

Board Regular
Joined
Jul 4, 2013
Messages
76
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

Well-known Member
Joined
May 11, 2012
Messages
2,456
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,875
Messages
5,627,407
Members
416,245
Latest member
Xterminat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top