VBA Excel Passing an Button as Argument

Question

I want to pass a CommandButton as an argument.

Example:

Sub calc(btn as button)
 btn.Caption = "Something"   
End Sub

Private Sub CommandButton1_Click()
 calc(CommandButton1)
End Sub

Private Sub CommandButton2_Click()
 calc(CommandButton2)
End Sub

Is something like the above possible? If yes how can I do it?

edit

Thanks for your response, but I dont get it. So it looks like this now:

Public Sub calc(ByRef btn as Object)
 btn.Caption = "Something"   
End Sub

Private Sub CommandButton1_Click()
 calc(CommandButton1)
End Sub

Private Sub CommandButton2_Click()
 calc(CommandButton2)
End Sub

Maybe someone can explain it to me in more detail, because Im very new to VBA.

Alex K. · Accepted Answer · 2014-02-21 12:08:00Z

3

You need:

Sub calc(btn As MSForms.CommandButton)
    btn.Caption = "Something"
End Sub

And you must invoke it following the rules:

 calc CommandButton1        // best

 call calc (CommandButton1) // ok but verbose

 calc (CommandButton1)      // type mismatch!

(The type mismatch is because the parentheses evaluate CommandButton1 which results in its default property (a string) which is incompatible with the method argument type)

answered Feb 21, 2014 at 12:08

Alex K.

177k32 gold badges276 silver badges299 bronze badges

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

Comments

MLDev · Accepted Answer · 2014-02-21 07:46:32Z

0

This is the sub:

Public Sub temp(ByRef cmdb As Object)
cmdb.Caption = "somethine else"
End Sub

This is how you would call it

call sub (commandbutton_1)

edited Feb 21, 2014 at 7:46

answered Feb 21, 2014 at 7:29

MLDev

1,2774 gold badges13 silver badges23 bronze badges

4 Comments

Eugen Over a year ago

Thanks for your response, but I dont get it. So it looks like this now: Public Sub calc(ByRef btn as Object) btn.Caption = "Something" End Sub Private Sub CommandButton1_Click() calc(CommandButton1) End Sub Private Sub CommandButton2_Click() calc(CommandButton2) End Sub Maybe someone can explain it to me in more detail, because Im very new to VBA.

MLDev Over a year ago

changed it a bit, but don't how much easier it could explain it :), If it still doesn't make sense email me your file and I can take a look at it for you

Eugen Over a year ago

thank you very much! It works. I need to read some more about the Call keyword.

MLDev Over a year ago

np, glad I could help

Collectives™ on Stack Overflow

VBA Excel Passing an Button as Argument

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related