vba 来自其他工作簿的“单击”命令按钮

"Clicking" Command Button from other workbook

提问by Robert Mearns

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

我正在尝试编写一个宏来“单击”另一个工作簿中的命令按钮。那可能吗？不更改其他工作簿中的任何代码？

回答by Tristan Reischl

For an ActiveX button in another workbook:

对于另一个工作簿中的 ActiveX 按钮：

Workbooks("OtherBook").Worksheets("Sheet1").CommandButton1.Value = True

For an MSForms button in another workbook:

对于另一个工作簿中的 MSForms 按钮：

Application.Run Workbooks("OtherBook").Worksheets("Sheet1").Shapes("Button 1").OnAction

回答by Robert Mearns

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 OKbutton.

单击确定按钮。

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

回答by Ozgur Ozcitak

You can use Application.Runfor that:

你可以使用Application.Run：

Run "OtherWorkbook.xls!MyOtherMacro"

回答by Per Hornsh?j-Schierbeck

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()

回答by DP.

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
   

1. 在您拥有 的工作表 ABC 中Private Sub xyz_Click()，添加一个新的公共子程序：

   Public Sub ForceClickOnBouttonXYZ()
       Call xyz_Click
   End Sub
   

2. 在您的其他工作表或模块中，添加代码：

   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 = Falsebefore you call the Sheets("ABC").ForceClickOnBouttonXYZroutine and then set Application.ScreenUpdating = Trueright after it.

这就对了！！！如果您不希望屏幕闪烁或显示任何活动，请Application.ScreenUpdating = False在调用Sheets("ABC").ForceClickOnBouttonXYZ例程之前设置，然后Application.ScreenUpdating = True在它之后设置。

回答by Robert Ovington

I had a similar problem as above and it took a while to figure out but this is all I had to do.

我遇到了与上述类似的问题，花了一段时间才弄明白，但这就是我所要做的。

On Sheet1 I created a button with the following:

在 Sheet1 上，我创建了一个按钮，如下所示：

    Private Sub cmdRefreshAll_Click()
         Dim SheetName As String

         SheetName = "Mon"
         Worksheets(SheetName).Activate
         ActiveSheet.cmdRefresh_Click

         SheetName = "Tue"
         Worksheets(SheetName).Activate
         ActiveSheet.cmdRefresh_Click

'    "I repeated the above code to loop through a worksheet for every day of the week"

    End Sub

Then the importing bit to get it to work you need to go to the code on the other worksheets and change it from Private to Public.

然后导入位使其工作，您需要转到其他工作表上的代码并将其从私有更改为公共。

So far no bugs have popped up.

到目前为止，还没有出现任何错误。

回答by msulis

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).

但是，您可以通过打开工作簿，找到您想要的控件，然后激活它并向其发送一个空格字符（相当于按下按钮）来自动点击 VBA 代码。

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