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?

just ButtonExtractData_Click I think

Nathan_Sav
– Nathan_Sav

2017-05-11 17:09:30 +00:00
Commented May 11, 2017 at 17:09 — Nathan_Sav
– Nathan_Sav, Commented May 11, 2017 at 17:09

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

0

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"

answered May 11, 2017 at 17:43

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mathieu Guindon Over a year ago

Great! See how does accepting an answer work? if you're not sure what to do next ;-)

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related