How to simulate a command button click in another workbook (resolved)

[shaolang]

Harlow,

I have 2 workbooks and I want to simulate a click on one of the buttons in workbook A via some vba code in workbook B. How can I do that?

Thanks in advance.... :)

[Matt_T_hat]

workbook1.modulname.Button_onClick at a guess.

[shaolang]

That can't work, b'cos the buttons are drawn on a worksheet, not a userform. I can't modify the visibility of the button1_click() subroutine to public, which is kinda huge restrain.

[RobDog888]

You need to use the Shapes collection for buttons.

HTH

[shaolang]

But how exactly do you do that? I could get hold of the command button via the Shapes collection, but how do I make the shape "click"? Calling
worksheets(1).Shapes("commandbutton1").click
gives me a "method not found error".

[shaolang]

After a few days of searching, I've finally found the answer to the problem, and thought it might be useful to share in the forum.

There isn't a convenient way to simulate a click on a button that resides in a different worksheet where the code is (the only way is to use the Win32api), but since clicking the button is the same as pressing spacebar on the an activated button, I could just use the SendKeys() function. The code might look like this:

' code in worksheet B
dim obj as OLEObject ' will reference to a button in workbook A
set obj = Workbooks("A").ActiveSheet.Shapes(1).OLEFormat.Object
obj.Activate ' must activate the button so SendKeys() will send the spacebar to the intended button
SendKeys " " ' sends a spacebar, to simulate a click