Excel hyperlink base doesn't seem to work when used in template. Links changes automatically. Please help!

I thought the hyperlink base property would solve my problem, but I was to quick. It doesn't.

I have a button in one Excel workbook sheet pointing to a macro in another open workbook. This works fine. When I save this workbook as a template and move it along with the other workbook to another directory, open the other workbook, then open a new sheet based on my template and click the button, I get an error message telling me that Excel cannot find the macro in the sheet which was part of the original workbook in the original directory. In other words, it has preserved the original path/link to the macro, not just the filename and macro name as I entered it.

If I manually edit the link to the macro and simply remove the part of the link that is before the file name (Workbook filename!ThisWorkbook.Macro-name, it works fine.

When I saved the original workbook, I made sure to remove the path in front of the file name for all my buttons pointing to macros, but they seem to reappear no matter what I do, I also tried to add the corect path to the Hyperlink Base property, but that doesn't seem to have any effect at all.

What can I do the get rid of that path in front of the Excel filename!ThisWorkbook,Macro-name?

Please help!

Iver Erling ArvaSenior consultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Iver Erling ArvaSenior consultantAuthor Commented:
I suspect that this issue arose as a result of upgrading to Office 2013, but I'm not sure.

byundtMechanical EngineerCommented:
As a workaround, consider calling the macro in the other workbook from a sub in the workbook containing the hyperlink. As long as both workbooks are open and macros enabled in each, you can do this with code like this:
Sub IndirectHelloWorld()
Application.Run "'Hello World.xlsm'!HelloWorld"
End Sub

Open in new window

In the above snippet, there is a macro called HelloWorld in workbook Hello World.xlsm. This macro will run when IndirectHelloWorld is triggered.

Note the single quotes surrounding the name of the workbook containing the macro. These are necessary if the workbook name contains an embedded space, and are optional otherwise.
As a side note I would suggest not to put code called from buttons in the ThisWorkbook module, but in a normal module. In fact, try to keep code in Excel objects (ThisWorkbook, Sheet modules) to the bare minimum.
VBA modules can become corrupt and if the thisworkbook module becomes corrupt all you can do is recreate the workbook in question. Normal modules can be exported, deleted and imported to get rid of corruption.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Iver Erling ArvaSenior consultantAuthor Commented:
Thanks for your answers!

I have been thinking along the same lines as byundt suggests. My problem is that I have taken over support for an old system, so I am hesitant to change things unless I absolutely have to. This stuff used to work, and I haven't knowingly changed anything. All I have done is to open up and look at stuff in the macros and save. Also, the sheet with the buttons calling the macros is a xlsx-sheet and as far as I can tell cannot have any macros. I guess I will have to change that to use the workaround. I have simlar sheets (variations on a theme ;-) that do work!. Strange! I guess they were saved using Office 2010. Could that have anything to do with it? It is really strange that some sheets work and others don't.

Iver in Oslo
In my experience it does happen that buttons loose their 'connection' to the right workbook on occasion, even before Excel 2010. It is for that exact reason I wrote an article like this one (though it concerns UDFs):


Perhaps you can use the ideas in that article for your purpose?
byundtMechanical EngineerCommented:
Another possibility is to include a Workbook_Open sub in the template that "repairs" the links in all other open workbooks. The template could also include code that traps application-level events, such as the opening of a new workbook, and redirects those links as well.

After looking at jkpieterse's link, I see that he has anticipated those suggestions exactly, and included code that does the trick. When you read the webpage (and its four linked subpages), just think of macros instead of UDFs, and your template file instead of an add-in.
Iver Erling ArvaSenior consultantAuthor Commented:
This sounds promising! I will look into it at work tomorrow. Thank you!

Iver in Oslo
byundtMechanical EngineerCommented:
Though not documented by Microsoft, if your hyperlink references a user-defined function in the template workbook, you can call it with a HYPERLINK formula in the workbook that doesn't contain macros. By using the HYPERLINK function, you can specify the name of the other workbook and its UDF using text. As a result, there are no external links in that workbook. The HYPERLINK formula looks like this:
=HYPERLINK("#'Hello world.xlsm'!HelloWorld()","Hyperlink that calls a UDF")

In the above formula, a user-defined function HelloWorld in open workbook Hello world.xlsm is being called. Note the workbook name is enclosed by single quotes, and is preceded by a #. You'll get an error message if you omit the #.

When testing a user-defined function, I was surprised that it could change the user interface--I changed the color of an adjacent cell. I also found that there would be an error message if the HYPERLINK called a macro, or if the user-defined function didn't return a range reference. My workaround in the UDF below is to select the cell below the active cell.

I tested the code and HYPERLINK formula in Excel 2003 and Excel 2013--and was pleased to find that it works in both.
Function HelloWorld() As Range
Dim cel As Range
Set cel = ActiveCell.Offset(1, 0)
MsgBox "Hello world"
cel.Interior.ColorIndex = 6
Set HelloWorld = cel
End Function

Open in new window

Iver Erling ArvaSenior consultantAuthor Commented:
Very helpful thoughts and ideas. I will make note of them for future reference. Thank you!

Now, for my project. Just as suddenly as it broke, it started working again. I have absolutely no idea what changed, but both I and the users now can run it just fine again.

Strange! Really!!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Iver Erling ArvaSenior consultantAuthor Commented:
The only way I have found to fix this is to manually open each workbook and change the macro assignments and save. If I use a macro to create the sheets with macros, the links gets messed up.

Thanks anyway!
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.