CODEWITHSUNDEEP
excelvba
Osman Wong
Osman Wong

Reputation: 170

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

And the list go on

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

Something like below?

Call GetFile(triggering-commandbutton.name)

Upvotes: 0

Views: 1409

Answers (2)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19712

Form Controls

In a normal module add a procedure with no arguments: 

Public Sub Button_Click()
    GetFile Application.Caller
End Sub

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

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

ActiveX Controls

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

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

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.

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

Upvotes: 3

PaichengWu
PaichengWu

Reputation: 2689

You can create many commandbuttons as ActiveX controller, then write VBA like below:

Private Sub CommandButton1_Click()
    Call GetFile("Account1")
End Sub

Private Sub CommandButton2_Click()
    Call GetFile("Account2")
End Sub

Upvotes: 0

Related Questions