[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

Problem executing a macro in another open Workbook

I want to execute a macro called "Main" held in second workbook from macro in current workbook

current workbook code snippet is as follows:

Dim second_wb As Workbook
Set second_wb = ActiveWorkbook

Application.Run second_wb.Name & "!Main"

I get a "run-time error 1004: The macro 'second macro.xls!Main' cannot be found".

Any ideas anyone please?

Thanks
0
Zac5
Asked:
Zac5
  • 4
  • 4
1 Solution
 
Patrick MatthewsCommented:
Zac5 said:
>>Any ideas anyone please?

1) Make sure that Main is not declared as a Private macro, and/or that you have not used a module declaration
Option Private Module.

2) Change the call to:

Application.Run "'" & second_wb.Name & "'!Main"
0
 
Patrick MatthewsCommented:
Zac5,

Another thing to check:  is Main in a "regular" module, or is it in a class/UserForm/sheet/ThisWorkbook
module?

Regards,

Patrick
0
 
Zac5Author Commented:
Hi Patrick,

thanks for your reply, my guess would be a regular module looking at the tree structure on the left of Microsoft VB Editor, but I'm not sure, how can I tell you for certain?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Patrick MatthewsCommented:
Zac5 said:
>>my guess would be a regular module looking at the tree structure on the left of Microsoft VB Editor, but I'm
>>not sure, how can I tell you for certain?

If the module shows up under the "Modules" node, and not under the Forms, Class Modules, or Microsoft Excel
Objects nodes, then it's a regular module.
0
 
Patrick MatthewsCommented:
Zac5,

BTW, did you change the call to wrap the workbook name in single quotes, as I demonstrated in my first post?

Regards,

Patrick
0
 
Zac5Author Commented:
I had missed your first post on adding the quotes and tried it now, but still no luck unfortunately, still fails but this time with an error "Object required".  I have put it back to the way it was for now.
0
 
Zac5Author Commented:
and it is a regular module.
0
 
Zac5Author Commented:
Hi Patrick,
Thanks you resolved it in your first post! As you said, the quotes were needed. I made a mistake whilst adding your line to my code, I forgot to replace "second_wb" with the actual name in my code, hence the "obect required" error :-)
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now