How to Make 30 cmdButtons Run Same Code Without Adding Code to Each One

[Ken Charoen]

I have 30 CommandButtons on a UserForm in VBA Excel. When anyone of these buttons gets clicked, I want it to run the same function (See below).

How can I accomplish this without adding the function to each CommandButton like below?

Thanks very much for your suggestions.

Private Sub CommandButton1_Click()
    Call MyFunction
End Sub
 
Private Sub CommandButton2_Click()
    Call MyFunction
End Sub
 
..........
..........
..........
 
Private Sub CommandButton30_Click()
    Call MyFunction
End Sub

[Killer42]

To be honest, I don't think you can.

In VB6 (and earlier versions), it's perfectly simple. You just create them as a control array, and they all share the same event procedures. It's wonderful.

For unfathomable reasons, M$ have apparently decided to remove this functionality from VBA and later (.Net) versions of VB. There are ways to simulate a control array in your code, but I don't know of any way to get around the need for individual event procedures like yours.

One suggestion. It might be a good idea to pass the control name to MyFunction. Just in case you ever need to know which button was clicked. In fact, I suspect the simplest thing would be to pass the actual button control to the function. That way, you can do whatever you like with it.