Group 400 ToggleButtons in Excel and assign just 1

Question

Here's what I need: I have a Toggle Button. I wrote a small VBA Code in Sheet1 to make this button turn green and show caption "G" when pressed, or turn yellow and show caption "Y" when depressed. Works great.

But I have 400 of them on Sheet1 I need to apply this code to. I don't know how to use UserForm, I can't figure out macros, and manually entering the code was both tedious and corrupted all my ComboBox entries.

Sub ToggleButton1_Click()
    If ToggleButton1.Caption = “G” Then
        ToggleButton1.Caption = "Y"
    Else
        ToggleButton1.Caption = "G"
    End if
    If ToggleButton1.Value = True Then
        ToggleButton1.BackColor = vbGreen
    Else
        ToggleButton1.BackColor = vbYellow
    End If
End Sub

I know the code I've just entered looks sloppy at best (I don't even know how to use the format for the forum). But any pointers would be appreciated.

Answer 1

My suggestion would be to create a class module, name it clsToggle

Option Explicit

Public WithEvents TGButton As ToggleButton

Private Sub TGButton_Click()
    With TGButton
        If .Caption = "G" Then
            .Caption = "Y"
        Else
            .Caption = "G"
        End If
        If .Value = True Then
            .BackColor = vbGreen
        Else
            .BackColor = vbYellow
        End If
    End With

End Sub

Then add in a normal module the following code

Option Explicit

Dim TBTs() As New clsToggle

Sub Init_ToggleButtons()

Dim obj As OLEObject
Dim TGCount As Integer
    TGCount = 0

    For Each obj In ActiveSheet.OLEObjects
        If TypeOf obj.Object Is ToggleButton Then
            TGCount = TGCount + 1
            ReDim Preserve TBTs(1 To TGCount)
            Set TBTs(TGCount).TGButton = obj.Object
        End If
    Next

End Sub

Run the Sub Init_ToggelButtons and all the Togglebuttons will react to the click event defined in the class module. And of course remove your code you may already have defined for your togglebuttons.

Edit: You need to run Init_ToggelButtons every time you open the workbook. So it's best to add the following sub in the module

Sub Auto_open()
    Init_ToggleButtons
End Sub