Boggled by Toggles ... about 400 of them

[ThatsEarElephant]

Hello! Before anybody asks me if I'm crazy for having a spreadsheet of 400 toggles (not to mention 2000 ComboBoxes), the short answer is "yes, I do need them!". But obviously I don't want to code every single item (which just bogs Excel up). Here is what I want to do:
I have a toggle button. When it's pressed, I want it to turn Green and caption "G". When it's released, I want it to turn Yellow and caption "Y". I know how to do this. Now, how do I make a group of these to all do the same thing (without UserForm, preferably). My script for one ToggleButton is this: ***Oh...please go easy on me. I'm new and I don't know anything about VBA syntax.

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

 

[ThatsEarElephant]

If I could send a pic from my desktop I would...sorry.

 

[ThatsEarElephant]

I wish I could post a snipit of what I am talking about. Cells B10 to B176 are populated with toggle buttons (Yellow denotes bad weather status for flying, Green denotes good weather for flying). If I write a code, I have to reference every single toggle button in the VBA window. I must copy and paste the code and change each one to reference ToggleButton1, ToggleButton2, ToggleButton3...et cetera. I don't want to do that. I want to group ToggleButton1-thru-ToggleButton200 so I can write one code, not a bunch. Am I getting closer?

 

[NoSparks]

@ ThatsEarElephant
If the toggle buttons didn't exist at all, would your users have the ability to double-click the cell the toggle buttons would have been in?

 

[My Aswer Is This]

Show us the script your putting in the Toggle Button.

And there must be more to it then just toggling the color of the button and the caption.

The big question is "Why does the user decide to toggle the button". The user must make a decision on to click the button or not. What is that decision based on?

 

[ThatsEarElephant]

Oo! Someone from another site wrote a code...which works beautifully. Only issue now is when I share my workbook it won't run the script, even after macros are enabled. Stumped.

'In A Class Module
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


'And in a regular module
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

 

[ThatsEarElephant]

No, there was really no more to it as you said. The function of my workbook or which buttons I chose to use wasn't in question - I just needed to know how to perform a function. But, I got the answer I needed anyways. Thanks.

 

[My Aswer Is This]

Well glad you have what you want. Take care.

No, there was really no more to it as you said. The function of my workbook or which buttons I chose to use wasn't in question - I just needed to know how to perform a function. But, I got the answer I needed anyways. Thanks.

 

[Michael M]

@ThatsEarElephant
Try uploading an image to wither dropbox or similar.
Then post the link back here to that site

 

[ThatsEarElephant]

@Michael M - I'm sorry...I don't know what that is But I got the above code to work in Class Module & a regular Module. Just need to figure out how to get my macros to run when I share this file with other users.

 

[Norie]

Couldn't you use BeforeDoubleClick for this?

For example this will toggle between G with green interior and Y with yellow interior when the user double clicks in the range B10:B176.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Not Intersect(Target, Range("B10:B176")) Is Nothing Then
        Cancel = True
        With Target
            If .Value = "G" Then
                .Value = "Y"
                .Interior.Color = vbYellow
            Else
                .Value = "G"
                .Interior.Color = vbGreen
            End If
        End With
  
    End If

End Sub