使用具有按钮属性的不同命令按钮触发相同的宏 [英] Trigger same macro with different command button with Button properties

问题描述

正如主题所暗示的，我已经构建了一个需要输入变量的宏

As the subject suggest, I have build a macro that will need a variable input

Sub GetFile(Account as String)

当前，我正在设置多个子菜单来调用脚本并将它们分配给不同的命令按钮

Currently i am setting multiple sub to call the script and assign them to different command buttons

Sub AC1_File
    Call GetFile("Account1")
end sub

Sub AC2_File
    Call GetFile("Account2")
end sub

列表继续

我正在尝试不让我的代码过长(因为我拥有两位数的帐户)， 我的代码能否获取命令按钮名称的属性并使用它代替Account1,2,3 ...?

I am trying to not make my code too long (as i got double digits accounts), is it possible for me code to get properties of the commandbutton name and use it instead of Account1,2,3...?

像下面这样吗?

Call GetFile(triggering-commandbutton.name)

推荐答案

表单控件

在普通模块中，添加一个不带参数的过程:

Form Controls

In a normal module add a procedure with no arguments:

Public Sub Button_Click()
    GetFile Application.Caller
End Sub  

此代码将获取按钮名称，并将其传递给GetFile过程.

This code will pick up the button name and pass it to the GetFile procedure.

Sub GetFile(Account As String)
    MsgBox "Account Name is " & Account
End Sub  

因此，现在您要做的就是将按钮命名为 Account1 ， Account2 等.

So now all you need to do is name your buttons Account1, Account2, etc..

如果您使用的是ActiveX控件，则可以使用一个类来捕获click事件.

If you're using ActiveX controls you can use a class to capture the click event.

创建一个类模块并将其命名为clsButtonClick.
将此代码添加到类模块:

Create a class module and name it clsButtonClick.
Add this code to the class module:

Public WithEvents AccountBtn As MSForms.CommandButton

Private Sub AccountBtn_Click()
    MsgBox AccountBtn.Name & " on " & AccountBtn.Parent.Name
    GetFile AccountBtn.Name
End Sub

在普通模块的顶部添加以下行:

At the very top of a normal module add this line:

Public colBtns As New Collection  

并将此代码添加到模块中:

And add this code to the module:

Public Sub Initialize_Buttons()

    Dim wrkSht As Worksheet
    Dim btnEvnt As clsButtonClick
    Dim obj As OLEObject

    For Each wrkSht In ThisWorkbook.Worksheets
        For Each obj In wrkSht.OLEObjects
            If TypeName(obj.Object) = "CommandButton" Then
                Set btnEvnt = New clsButtonClick
                Set btnEvnt.AccountBtn = obj.Object
                colBtns.Add btnEvnt
            End If
        Next obj
    Next wrkSht

End Sub  

这将遍历工作簿中的每个工作表，并为所有ActiveX命令按钮提供来自类模块的click事件.
如果需要将Initialize_Buttons过程限制为特定的工作表或工作表上的特定按钮，请更新此过程.

This will go through each sheet in your workbook and give any ActiveX command buttons the click event from the class module.
Update the Initialize_Buttons procedure if you need to limit it to a specific sheet, or specific buttons on a sheet.

最后在ThisWorkbook模块的Workbook_Open事件中调用Initialize_Buttons代码.

Finally call the Initialize_Buttons code in the Workbook_Open event in the ThisWorkbook module.

这篇关于使用具有按钮属性的不同命令按钮触发相同的宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！