Running a macro using a macro in another workbook

[pp57]

I have a workbook that contains a large number of worksheets, one of which contains a number of button objects. Clicking one of the buttons (let's call it the "calculate button") begins a series of calculations based on numbers in that worksheet which itself reads inputs from throughout the workbook. It currently functions as I want it to without any issues.

I have created a second workbook with which I would like to run a loop that modifies values in the original workbook and "click" the calculate button after each combination of values is entered. It would likely be easier to do all of this with one workbook but I have to keep them separate as the first workbook is for public use and the second one is just for me.

I've created a simplified version of the scenario and attached the two files. I would like to click the button in 'Results Worksheet.xlsm" and have it then run the macro in 'Calculation Worksheet.xlsm'. Currently I keep getting the error shown below:

macroError.JPG

Hopefully someone has some ideas about how to fix this. Thanks in advance.

[Norie]

Can you post the code you are using to try and run the code behind the button?

[pp57]

Here is the code that is in the "independent" worksheet:

Please Login or Register  to view this content.

Here is the code that's supposed to call it from the second workbook:

Please Login or Register  to view this content.

[Norie]

Why are you using Do_This to try and run a sub called Do_This_Click?

[pp57]

Why are you using Do_This to try and run a sub called Do_This_Click?

Oops, my mistake. Fixed it. Same result though.

Please Login or Register  to view this content.

[Norie]

Where is the sub Do_This_Click located?

Does it work if you declare that sub as Public rather than Private?

PS Is the space between the '!' and 'Do_This_Click' a typo?

[pp57]

Changing it to Public yields the same result.

The space after the "!" was based on advice that I found doing an initial search for a solution but I'm actually not sure if it's necessary. Removing the space doesn't change the result though.

[Norie]

There shouldn't be a space between the ! and the name of the procedure you are trying to call.

The procedure you are trying to call should be in a standard module and be declared as Public.

[pp57]

And that fixed it. Thanks!

[pp57]

Actually, new issue. Now I can't get the Do_That_Click procedure to run directly from the workbook that it's in. It only works now by calling it from the second workbook. Is there a different way that I should be called the button-activated procedure now that it's located in the standard module instead of the worksheet?

[Norie]

What does the button actually do?

Does it call other code?

[pp57]

Yes, that's the intention. My original post has more detail but in short in this simple example I want both buttons to produce the same result but it's important that the workbooks be separate.

In my actual workbooks that these are based on one of the buttons will also loop through a series of input combinations.

[Norie]

Sorry I'm a little confused.

If all the button does is call another sub why aren't you calling that sub instead of the button's click event sub?

For example, let's say the code for your button looked like this and it calls a sub DoItAlready which is in a standard module and not declared as Private.

Please Login or Register  to view this content.

If this was the case rather than trying to call Do_That_Click with Application.Run you would call DoItAlready.

Please Login or Register  to view this content.

[pp57]

Sorry, this is a little difficult to explain just written out. I have two documents ResultsWB and CalculationWB. CalculationWB has a button that runs a sub Do_This_Click that performs a calculation. It doesn't call another sub. ResultsWB has a button which runs a sub which calls Do_This_Click.

I was hoping to not have Do_This_Click call another sub as that will make the structure more complicated than I currently have it setup. Is there no way to have Do_This_Click be a public sub that runs both when the button in CalculationWB is clicked and when the button in ResultsWB is clicked?

I'm attaching the updated files to show the current state of the issue. In the attached files, "Do That" produced the correct result but "Do This" does not.

[Norie]

You shouldn't really be calling event code but this will work if the code for the button on 'Calc Sheet' is in the worksheet module.

Please Login or Register  to view this content.

[pp57]

Okay, this is working now for the version of my code that I attached in this post. Unfortunately the full version is now getting the following error:

Run-time error '1004':

Method 'Run' of object '_Application' failed

The line that is producing this error is:

Please Login or Register  to view this content.

Is there anything obvious about that line of code that would case this error that I'm missing? Unfortunately I can't attach much more than that since that would involve a few hundred lines of code and some information that I can't share.