Calling a macro in another open Excel workbook, Excel automatically adds path and hence cannot find macro.


This happens in Excel 2013:

1. I open a macro enabled workbook A
2. In that workbook A, I open workbook B using a macro calling a template B0.
3. Both workbooks A and B0 are stored in the same directory C:\Excelstuff\myDir
4. In Workbook B, there is a button connected to a macro.
5. The macro is stored in Workbook A.
6. In the properties of the button in workbook B0 (tablet), the reference entered is to WorkbookA!This workbook.Macro1
7.When I try to click the button in the newly opened workbook A, it tells me that the macro cannot be found. The reason is that the path C:\Excelstuff\myDir is automatically added in front of WorkbookA!ThisWorkbook.Macro1

Can anyone please help!

Iver Erling ArvaSenior consultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

[ fanpages ]IT Services ConsultantCommented:
That behaviour sounds like it should be happening; i.e. fully-qualified filenames to ensure the correct routine is being executed.

In your question you seem to swap between referring to "B0" as a 'template', a 'tablet', & a 'workbook', so am I not entirely sure what it being called within "B0", but can you move "Macro1" in "WorkbookA" from 'ThisWorkbook' to a Public code module to establish if the reason for it not being found is because it is Private to "WorkbookA"?

pls verify the spelling of your "assign to" macro

Iver Erling ArvaSenior consultantAuthor Commented:
I think I may have found the solution to my problem.

In Excel:

1. Open the Excel tablet that has the problem.
2. On the Developer tab, click Macro security.
3. In the window that opens, select External contents in the menu on the left side.
4. Select Deactivate automatic update...
5. Close window.
6. Edit all macro assignments (buttons connected to macros) to remove the added path.
7. Save.

Now it works as expected.


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:
Now it works as expected.
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.