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

 

[Norie]

How did you create the toggle buttons?

 

[ThatsEarElephant]

I used an ActiveX Control

 

[ThatsEarElephant]

Under the Developer Tab --> Insert --> ActiveX Control

 

[My Aswer Is This]

You said:

I'm new and I don't know anything about <acronym title="visual basic for applications">VBA</acronym> syntax.

But your sure you need 2,000 Comboboxes and 400 Toggle buttons.

Would you care to tell us what your attempting to do here?
You may discover someone here may have a better plan for what your attempting to do.

 

[ThatsEarElephant]

I promise I do need them all...it's for a database at work.
I will leave the ComboBox issue aside for another forum, but right now the Toggle Button issue is what concerns me.
In this list, there are 400 different assets who, at the beginning of their shift, denote whether they are "Green" status or "Yellow" status. The toggle button is to clarify on this roster who is yellow and who is green. I originally had one of those lousy drop-down menus where, by picking Green or Yellow, the cell would turn to the corresponding color. Unfortunately (and I hate to put it this way) many users I'm making this database for aren't very articulate and if they fat-finger or misspell something in that field for "Yellow or Green" and the cell doesn't populate a color based on their mistakes, well...I just need something super user-friendly and error-proof.

 

[ThatsEarElephant]

I hope someone has a better suggestion! I'm not getting very far!

 

[ThatsEarElephant]

My biggest mistake was building a tiny database with all these fancy Excel options for a small user group at my company, now the entire company really loves my database and the director wants to employ it into our work environment.

 

[My Aswer Is This]

If your willing to hear about other options maybe you could explain in detail what the option buttons were suppose to do.

You said:

In this list, there are 400 different assets who, at the beginning of their shift, denote whether they are "Green" status or "Yellow" status. The toggle button is to clarify on this roster who is yellow and who is green.

Where in the sheet is "this list" I suppose were talking about a certain column.
Any time you want help here we need exact details.

It appears you want to toggle cells interior color

Do you just want to toggle the color back and forth from Yellow to Green?

Or is there some cause and effect as to if the cells color should be Green or Yellow?

 

[ThatsEarElephant]

In This List = the column of toggle buttons I have alongside whatever list I have on the database - yes.
Correct. As I stated from the beginning of the thread, I need all 400 toggle buttons to operate so that, when clicking them, each one can individually turn green & denote "G" in the caption or yellow & denote "Y" in the caption, depending on whether the button was pushed or not. I know how to do that to every individual button, but I need to now how to write a VBA Code which will take care of ALL 400 buttons so I'm not doing a copy-and-paste 400 times in my VBA Code. Is any of this making sense?

You asked "..or is there some cause and effect as to if the cells color should be Green or Yellow?". Not quite sure I understood what you meant. Do you mean "cells" or by cells are you referring to toggle buttons?