"Clicking" Command Button from other workbook

Question

Hi I am trying to write a macro that would "click" a command button that is in another workbook.. Would that be possible? Without changing any of the code within that other workbook?
Thanks a lot!

Answer 1

You can use Application.Run for that:

Run "OtherWorkbook.xls!MyOtherMacro"

Answer 2

You sure you mean workbook and not sheet? Anyways if you "want to loop through all workbooks in a folder and perform an operation on each of them"

If you want to access another sheet it's done like this:

Worksheets("MySheet").YourMethod()

Answer 3

There's not a clean way to do this through code, since the button's click event would typically be a private method of the other workbook.

However, you can automate the click through VBA code by opening the workbook, finding the control you want, and then activating it and sending it a space character (equivalent to pressing the button).

This is almost certainly a terrible idea, and it will probably bring you and your offspring nothing but misery. I'd urge you to find a better solution, but in the meantime, this seems to work...

Public Sub RunButton(workbookName As String, worksheetName As String, controlName As String)
    Dim wkb As Workbook
    Dim wks As Worksheet
    Set wkb = Workbooks.Open(workbookName)
    wkb.Activate
    Dim obj As OLEObject
    For Each wks In wkb.Worksheets
        If wks.Name = worksheetName Then
            wks.Activate
            For Each obj In wks.OLEObjects
                If (obj.Name = controlName) Then
                    obj.Activate
                    SendKeys (" ")
                End If
            Next obj
        End If
    Next wks
End Sub

Answer 4

Instead of trying to programatically click the button, it is possible to run the macro linked to the button directly from your code.

First you need to find the name of the macro that is run when the button is clicked.

To do this, open the workbook that contains the command button.

Right click on the command button and select 'Assign macro'

The 'Assign macro' dialog will be displayed.

Make a note of the full name in the 'Macro name' box at the top of the dialog.

Click on the OK button.

Your code in the workbook that needs to call the code should be as follows.

Sub Run_Macro()

    Workbooks.Open Filename:="C:\Book1.xls"
'Open the workbook containing the command button
'Change  the path and filename as required

    Application.Run "Book1.xls!Macro1"
'Run the macro 
'Change the filename and macro name as required

'If the macro is attached to a worksheet rather than a module, the code would be
'Application.Run "Book1.xls!Sheet1.Macro1"

End Sub

Answer 5

NOPE:

This is EASY to do:

1. In the worksheet ABC where you have the Private Sub xyz_Click(), add a new public subroutine: Public Sub ForceClickOnBouttonXYZ() Call xyz_Click End Sub

2. In your other worksheet or module, add the code: Sheets("ABC").Activate Call Sheets("ABC").ForceClickOnBouttonXYZ

THAT IS IT!!! If you do not want the screen to flicker or show any activity, set the Application.ScreenUpdating = False before you call the Sheets("ABC").ForceClickOnBouttonXYZ routine and then set Application.ScreenUpdating = True right after it.