How to invoke a command button present in sheet 1 of book1 using VBA code from book2 module1

Question

I have a command button present in Sheet1 of book1 with name 'extract data' and the code is as mentioned below

Private Sub ButtonExtractData_Click()
'code 
End Sub

I have written the below code and it gives me error:

Cannot run the macro 'Book1.sheet1.ButtonExtractData_Click' macro may not be available in this workbook or all macros may be disabled.

Sub run_macro()
    Workbooks.Open Filename:="C:\folder1\book1.xlsm"
    Application.Run "book1.xlsm!Control.Sheet1.ButtonExtractData_Click"
End Sub

How can I trigger this button click from another workbook?

Mathieu Guindon Mathieu Guindon · Accepted Answer · 2017-05-11T17:43:57

You shouldn't be invoking Click handlers. Not from another workbook, not from the workbook it's implemented in, not from anywhere. Event handlers are invoked when events are fired by whatever object raises an event: they're Private for a reason - no other code should ever invoke them directly.

You wouldn't do it in Java, you wouldn't do it in C#, don't do it in VBA.

The handler is in a worksheet's code-behind; it's not a macro, it's an implementation detail that actually lives at instance level, for a worksheet module is a special kind of class module, and Sheet1 is actually an object - and Application.Run doesn't work with objects, it works with macros.

Take whatever code is in the body of that event handler, move it to a parameterless Public Sub in a separate "standard module" (.bas), and then make your click handler invoke that procedure.

Now you have a macro. Let's say you called it DoSomething. You can invoke it like this:

Application.Run "'book1.xlsm'!DoSomething"

How to invoke a command button present in sheet 1 of book1 using VBA code from book2 module1

1 Answers