Unique code for several CommandButtons in UserForm-CodePudding

I am trying to get the captions of several CommandButtons in an UserForm. I know how to get the caption for one CommandButton but I dont want to copy paste the code for all Commandbuttons like

Sub CommandButton1_Click()
CodeXYZ
End Sub

Sub CommandButton2_Click()
CodeXYZ
End Sub

....

Is it possible to make something like

Sub Master_CommandButton_Click()
ActiveSheet.Range("A1").Value = Master_CommandButton.Caption 

End Sub

Edit: I am dynamically loading the captions from a table (depending on row and column). If one of the command button get clicked the caption from the clicked button gets written to a cell.

Private Sub UserForm_Initialize()
CommandButton1.Caption = ThisWorkbook.ActiveSheet.Range("B2").Value
CommandButton2.Caption = ThisWorkbook.ActiveSheet.Range("C2").Value
CommandButton3.Caption = ThisWorkbook.ActiveSheet.Range("D2").Value
CommandButton4.Caption = ThisWorkbook.ActiveSheet.Range("E2").Value
CommandButton5.Caption = ThisWorkbook.ActiveSheet.Range("F2").Value
End Sub

Private Sub CommandButton1_Click()
ThisWorkbook.ActiveSheet.Cells(5, 5).Value = CommandButton1.Caption
End Sub

What I know need is not to define every button itself. I want to hand over a a mastercode to all buttons.

CodePudding user response：

You could use a class module to handle the click event of all the command buttons on the form.

Here's one way to do that.

Create a class module named CCmdBtns and add this code to it.

Option Explicit

Public WithEvents CmdGroup As MSForms.CommandButton

Private Sub CmdGroup_Click()
    ThisWorkbook.ActiveSheet.Cells(5, 5).Value = CmdGroup.Caption
End Sub

In a standard module add this code.

Option Explicit

Public CmdButtons() As New CCmdBtns

Now in the userform module you can add this code.

Option Explicit

Private Sub UserForm_Initialize()
Dim ctl As MSForms.Control
Dim cnt As Long
    
    For Each ctl In Me.Controls
        If TypeName(ctl) = "CommandButton" Then
            cnt = cnt   1
            ctl.Tag = ctl.BackColor
            ReDim Preserve CmdButtons(1 To cnt)
            Set CmdButtons(cnt).CmdGroup = ctl
        End If
    Next ctl
    
End Sub

Now when you click any of the command buttons on the userform it's caption will be written to E5 on the active sheet.

Note you could simplify the code for setting the captions by using a loop.

Dim idx As Long

    For idx = 1 To 5
        Me.Controls("CommandButton" & idx).Caption = ThisWorkbook.ActiveSheet.Cells(2, idx   1).Value        
    Next idx