0

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)
1
  • 1
    Just to check, these are ActiveX command buttons, as opposed to a Form Control button? With a Form Control button, you could use Application.Caller. More complex with ActiveX, but doable. Commented Dec 12, 2018 at 9:39

2 Answers 2

3

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.

Sign up to request clarification or add additional context in comments.

Comments

0

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

2 Comments

the sub or function of GetFile() is actuallly written inside the command button or inside the ThisWorkBook object ?
@gumuruh OP didn't mention about that. But, usually I will write them in a normal Module.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.