How to make "traffic light" buttons in Excel?

Factual Questions
1

Calling any Excel users including the “Ask the Excel guy” if he’s still around.

I have a project tracking spreadsheet and my manager wants “traffic light” type buttons. You know, green (on track), yellow (in progress/potential risk), red (risk) and maybe another color (not started). Heck, even simpler, green (done), yellow (in progress) and red (in trouble).

My manager doesn’t like just highlighting the cell. It has to be buttons.

In conditional formatting, I figured out how to hack this. You have to put in a 0 for red, 1 for yellow and 2 for green.

What I really want to do is just have a button in the cell, and with every mouse click it just cycles through the colors. In other words, there is a button, I click it, and the color changes, and I (and manager) can just click to change.

I use Excel 2013.

2

This may be more of a Visual Basic question than an excel question. Excel, as far as I know, cannot do this WITHOUT adding some some visual basic to it.

There is also THISbut it’s not exactly what you’re asking for…

3

Conditional Formatting can do this with Icon Sets. There are traffic lights built-in.

4

Reply - thanks, that’s the same hack I came up with. I checked the box to only show the button. It works fine for me, but I’d like to have a button to just click on instead of entering in a number value. One would think this is straightforward but a websearch failed me.

5

How about a data validation drop-down list instead of buttons? Two clicks max for the user, instead of 3-way toggle button. Is that close enough?

If a button is absolutely necessary, like Frumpy said you could probably do it as a VBA macro.

ETA: Here’s another drop-down list tutorial that might be easier to follow.

(There might be ways to do radio buttons too)

6

Stick in a bunch of check boxes, link each to a cell value (linked cells will say TRUE or FALSE), then format the linked cells.

7

Obviously would only work for two colors, though.

8

Will he accept radio buttons? They’re still 1-click. I made a file with 4 radio buttons and a cell that changes colour depending on which radio button you select: http://dl.dropbox.com/u/38027026/radio%20buttons.xlsx (I just learned to use option buttons, still haven’t quite figured them out)

9

Ok. How did you do that? I haven’t come across these in Excel before.
J.

10

I’d seen drop-down lists in Excel before. I used http://office.microsoft.com/en-001/excel-help/add-a-check-box-option-button-or-toggle-button-to-a-worksheet-HP010236677.aspx, which wasn’t very useful as it didn’t tell you how to customise or add more options. http://stackoverflow.com/questions/267668/how-do-i-set-up-radio-buttons-in-excel helped a bit more by saying you need to add a “Group Box” first, but I still can’t figure out how to really link options.

11

As a humorous sidebar, I once dealt with an Executive/Managerial Dashboard type application (not Excel) that was supposed to display the status of business activities in an easy to read format to help high level decision makers determine at a glance if stuff is going as hoped or if there were problems. It used traffic light colors but didn’t quite keep the metaphor - the “green” was a green check mark, the “yellow” was a yellow exclamation point, and the “red” was a red X. I so so wanted to add a black Skull and Crossbones/Jolly Roger to indicate that a project was so behind schedule that there was no hope. My other choice would have been a resume - so the options are Green Check (Tells the manager that they are doing a good job), Yellow Exclamation point (rough points encountered but not too serious), Red X (Manager needs to take action), and Resume (Warning to the manager that things are so bad that they are going to be fired).

12

Would colored text or colored background work? You can easily change them, either by directly formatting them or by using conditional formatting to change the color if some specified values change.

13

Is the color driven by data? If so you might want to have a look at microcharts, which integrates into excel and gives tiny visualization a of data trends right inside each cell.

http://www.inovista.com/overview/MicroCharts.html

14

What about a drop-down list of “green”, “yellow” and “red” (and/or whatever other colors you may want), and set conditional formatting to fill the cell with whatever color that is?

15

My manager wants buttons. It would be much simpler if they could accept just a colored cell like I originally made. Nope, needs to be a “traffic light.”

So, I have a traffic light using the conditional formatting. type 0 for red, 1 for yellow and 2 for green.

As a shareholder, I weep

16

I found this page -
http://productive-information.co.uk/T_BasicTrafficLight.php

Further detail for use in Excel -
http://productive-information.co.uk/T_BasicTrafficLightUserGuide.php

I’m on my phone so can’t verify if it works or not.

17

Did the drop downs and radio buttons not work?

18

http://www.4shared.com/file/lDnilGAp/Color_Change_Button.html

Here is something I just started playing around with. I got one button to cycle through white-red-yellow-green-black and back to white.

I have not tried getting it to work for multiple buttons. Right now, it just uses the default CommandButton1_Click event. You could just copy the code for each button you want to use, but I think the best solution would be to figure out how to override the _Click event and get every button to call that same function. Unfortunately, I can’t work on it any more tonight, but I’ll see if inspiration comes to me while I sleep. :slight_smile:

Oh, and if you’re not comfortable reading the VBA: the button is linked to the cell in which its top-left corner is resting. My code then references the cell to the left of that cell. If you want it to actually change the cell in which it is anchored, that is easy to do.

And just to clarify: you really want an entire bank of buttons hovering right over your worksheet(s)? I’m thinking one command pane would be a lot simpler and cleaner. (And yes, I know this isn’t about what you really wanted.)

19

Ok, I think I have something that will do what you want.

Here is the file:
http://www.filedropper.com/colorchangebutton

I don’t know how comfortable you are in Excel, adding command buttons and working with VBA. Here is a quick description, but let me know if you need any other pointers:

  1. Open the file linked above and export the “Color_Change_Button” module, then import it into your workbook. Close my file.
  2. In your workbook, add a command button. Be sure to click on the cell to the right of the cell that you actually want to change (unless you adjust the code).
  3. Make sure that you are in design mode (on the Developer tab) and right-click on the new button. Select “View Code”. You should be taken to the VBA editor, inside the CommandButtonX_Click subroutine.
  4. Add this line:

Call Color_Change(CommandButtonX)

where X is whatever number command button you are working on.

You might need to save your workbook (as a macro-enable workbook if it isn’t already), close it, and reopen it before the macro will work.

20

Sorry, I can’t help you.
I use to be a LOT better in excel, but my boss only likes certain things, so that’s all I know now… I use to put in little extras, but he didn’t like “the clutter”.
But, he’s really good at asking for things like your traffic lights - “oh, sure, that would work, but… I’d really like it to do X” (always followed with “I’m sure it can be done.”)
So I spend time trying to figure out the obscure… and then get asked why I’m behind on other projects… someday I’ll tell him “It’s because you told me to try and get X to work”. Someday…