Excel

Group All Toggle Buttons on a Userform

Ease of Use

Hard

Version tested with

2002 

Submitted by:

Jacob Hilderbrand

Description:

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

Discussion:

You are making a userform with many Toggle Buttons. In this example we have the beginnings of a Keno game with 80 Toggle Buttons. You need code to run whenever any Toggle Button is pressed, but you don't want to repeat that code 80 times (once for each Toggle Button). A Class module will allow you to group all Toggle Buttons and write one macro that will be applied to every Toggle Button. 

Code:

instructions for use 

			
 '<In A Class Module>
Option Explicit 
 
Public WithEvents ToggleGroup As ToggleButton 
 
Private Sub ToggleGroup_Change() 
     
    MsgBox "You pressed " & ToggleGroup.Caption & vbCrLf & _ 
    ToggleGroup.Caption & " now has a value of " & ToggleGroup.Value 
     
End Sub 
 
Private Sub ToggleGroup_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) 
     
    Userform1.Label1.Caption = ToggleGroup.Caption & "  -  " & ToggleGroup.Value 
     
End Sub 
 
 '<In A UserForm>
Option Explicit 
 
Dim Buttons() As New Class1 
 
Private Sub UserForm_Initialize() 
     
    Dim ToggleCount     As Integer 
    Dim Ctl             As Control 
     
     
    For Each Ctl In UserForm1.Controls 
        If TypeName(Ctl) = "ToggleButton" Then 
            ToggleCount = ToggleCount + 1 
            ReDim Preserve Buttons(1 To ToggleCount) 
            Set Buttons(ToggleCount).ToggleGroup = Ctl 
        End If 
    Next Ctl 
     
End Sub 
 
 '<For Worksheet of Choice>
Option Explicit 
 '  This code will call the userform from the button on your worksheet
Private Sub CommandButton1_Click() 
    UserForm1.Show 
End Sub

How to use:

  1. Open Excel.
  2. Press Alt + F11 to open VBE.
  3. Insert | Class Module.
  4. Paste the code from above (The section marked for the Class Module Only).
  5. Insert | Userform.
  6. Double Click the userform to view the code section and paste the code from above (The section marked for the UserForm Only).
  7. Add as many Toggle Buttons to the User Form as you would like. Add Toggle Buttons from the Control Toolbox.
  8. Add one label from the Control Toolbox - it identifies the toggle button value on mouse over.
  9. Double click the Sheet1 Module in the left-hand pane.
  10. Paste in the Code marked For Worksheet of Choice.
  11. Close the VBE (using the red X button or Alt + Q)
 

Test the code:

  1. Go to the worksheet that you put the For Worksheet of Choice code into.
  2. From the View menu select Toolbars > Control Toolbox. With the Control Toolbox visible, drag a command button onto your worksheet. Exit Design mode by clicking the little cyan-colored triangle button.
  3. Press the command button on the worksheet. The Userform should now be visible.
 

Sample File:

ClassModuleToggleButton.ZIP 11.14KB 

Approved by mdmackillop


This entry has been viewed 154 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express