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

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)

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

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Related