VBA Express : Excel - Group All Command Buttons on a Userform

Excel

Group All Command Buttons on a Userform

Ease of Use

Hard

Version tested with

2002,2016

Submitted by:

Jacob Hilderbrand

Description:

Group all Command Buttons and write code that will apply to every member of the group instead of writing code for each specific Command Button.

Discussion:

You make a userform with many, many, many Command Buttons. When a user presses the Command Button you want to have the code do something (based on which Command Button was pressed) but you don't want to repeat the code for each Command Button. A Class Module will allow you to group all these Command Buttons and you only will need to write the code once to control all of them.

Code:

instructions for use

			 '<In a Class Module named CommandButtonClass>
Option Explicit 
 
Public WithEvents CommandButtonGroup As CommandButton 
 
Private Sub CommandButtonGroup_Click() 
    MsgBox "You pressed " & CommandButtonGroup.Caption 
End Sub 
 
Private Sub CommandButtonGroup_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) 
    Dim Ctrl As Control 
    For Each Ctrl In UserForm1.Controls 
        If TypeName(Ctrl) = "CommandButton" And Ctrl.BackColor = vbRed Then 
            Ctrl.BackColor = UserForm1.BackColor 
        End If 
    Next 
    CommandButtonGroup.BackColor = vbRed 
    UserForm1.Caption = CommandButtonGroup.Caption 
End Sub 
 
 '<In A UserForm>
 
Option Explicit 
 
Dim Buttons() As New CommandButtonClass 
 
Private Sub UserForm_Initialize() 
    Dim Ctrl As Control 
    Dim Count As Long 
    For Each Ctrl In UserForm1.Controls 
        If TypeName(Ctrl) = "CommandButton" Then 
            Count = Count + 1 
            ReDim Preserve Buttons(1 To Count) 
            Set Buttons(Count).CommandButtonGroup = Ctrl 
        End If 
    Next 
End Sub

How to use:

Open Excel.
Press Alt + F11 to open VBE.
Insert | Class Module and rename to CommandButtonClass
Paste the code from above (The section marked for the Class Module Only).
Insert | Userform.
Double Click the userform to view the code section and paste the code from above (The section marked for the UserForm Only).
Add as many Command Buttons to the User Form as you would like. Add Command Buttons from the Control Toolbox.

Test the code:

Refer to the "How To Use" section.
Download the attached file to see a working example.

Sample File:

ClassModuleCommandButtons.ZIP 9.45KB

Approved by mdmackillop

This entry has been viewed 323 times.