Code to rationalise multiple command buttons in VBA

**newbi004** · 11-16-2012, 05:25 AM

Hi there

Would anybody be able to advise me on my problem?

I have 63 command buttons on a userform in multipage controls. Each command button, when clicked, does almost the same thing. For example:

Private Sub cmd2_1_1_Click()
    
    If cmd2_1_1.BackColor = 13485434 Then
    Exit Sub
    End If
    
    cmd2_1_1.BackColor = 13485434
    
    CCaption = cmd2_1_1.Caption: ECaption = "Loft Quilt ": Supplier = "Central Purchasing": GLCode = "5002"
    
    Call ButtonClick
    
End Sub

Private Sub cmd2_1_2_Click()
    
    If cmd2_1_2.BackColor = 13485434 Then
    Exit Sub
    End If
        
    cmd2_1_2.BackColor = 13485434
       
    CCaption = cmd2_1_2.Caption: ECaption = "Loft Quilt ": Supplier = "Central Purchasing": GLCode = "5002"
    
    Call ButtonClick
    
End Sub

and so on. Is there a way to create a sub which will recognise the name of the command button, eg 2_1_1 for cmd 2_1_1 and 2_1_2 for cmd2_1_2 so that I can call that sub to execute the code? There is a different caption name for each button.

Thanks all.

**newbi004** · 11-16-2012, 06:40 AM

Any thoughts? I wonder if an event handler is the answer. Help!

**Kyle123** · 11-16-2012, 07:02 AM

I'd create an Event Handler because I couldn't be bothered adding code to each command button, but if you don't feel comfortable with classes, you might get away with something like:

Private Sub CommandButton1_Click()
    BtnClick Me.CommandButton1
End Sub
Sub BtnClick(ByRef btn As MSForms.CommandButton)

    If btn.BackColor = 13485434 Then Exit Sub
    btn.BackColor = 13485434
       
    CCaption = btn.Caption
    ECaption = "Loft Quilt "
    Supplier = "Central Purchasing"
    GLCode = "5002"
    
    ButtonClick
    
End Sub

Sub ButtonClick()
    MsgBox "sdfsdf"
End Sub

If you're interested, the event handler class would look something like:

Class CbtnHandler:

Public WithEvents btn As MSForms.CommandButton

Private Sub btn_Click()

    If btn.BackColor = 13485434 Then Exit Sub
    btn.BackColor = 13485434
    ButtonClick

End Sub

Private Sub ButtonClick()
    
    MsgBox btn.Caption

End Sub

Form implementation:

Dim btns As Collection


Private Sub UserForm_Initialize()
Dim oBtn As CbtnHandler
Dim ctl As Object

Set btns = New Collection

For Each ctl In Me.Controls
    If TypeName(ctl) = "CommandButton" Then
        Set oBtn = New CbtnHandler
        Set oBtn.btn = ctl
        btns.Add oBtn
    End If
Next ctl


End Sub

This approach would need no code in the button click subs