call macro from a different document

[smakatura]

I am creating a macro in New Outage OSP reporttemplate.xlsm. During this macro,I open document OSP Staffing All Voice.xlsm. the macro being created is New_Outage_macro.



OSP Staffing All Voice.xlsmhas a macro already on it called Private Sub cmdcalculate_Click().

I want to call the cmdcalculate_click macro in New_Outage_macro.

 

[Mentor82]

Hi,
You cannot call a macro from another module or workbook while its Private sub. It's a private sub because it's a button sub, isn't it? Do the following:
1. In the workbook OPS Staffing, insert a module, create there a sub CmdCalculate (), and paste there the code you have in
Private Sub cmdcalculate_Click()
2. In Private Sub cmdcalculate_Click() remove the code you have and place instead call CmdCalculate
3. In another workbook, the one in which you want to call a macro from workbook OPS Staffing call the CmdCalculate macro the following way:
Application.Run "'OSP Staffing All Voice.xlsm'!CmdCalculate"

Regards,
Sebastian

 

[smakatura]

my only trouble is that there is a chance that someone might overwrite osp staffing with a different document of the same name that would not have my new macro sub cmdcalculate().

but glad you mentioned that the private subs don't wo

 

[Mentor82]

The macro cmdcalculate_Click() that you currently have is in osp staffing workbook so you can get a rid if it if someone overwrites this document with another under the same name. To stave off this problem I recomment you move from that workbook to New outage osp or make a copy the macro and paste it to the new outage osp. You can call a private sub only within a partucular module/object(ex form) - outside those it's not available to make a call off.