I’ve written a piece of code for a similar boss where the user selects a text value of “red”, “amber” or “green” from a drop down, and as soon as they select it, the cell contents become a coloured circle of the appropriate traffic light. Not buttons, but to be honest, I’m struggling to think exactly hou buttons would work. I can dig it out if it would be helpful.
I was trying to solve this problem myself - I too had a manager looking to cycle through colors with the click of a button.
I thought I’d share a VBA solution I concocted. Simply assign the following to any shape, and that shape will cycle through the colors Green, Yellow, And Red when clicked.
Sub ChangeColor()
Dim WhoAmI As String, sh As Shape
WhoAmI = Application.Caller
With ActiveSheet.Shapes(WhoAmI).Fill.ForeColor
Select Case .RGB
Case vbRed
.RGB = vbGreen
Case vbGreen
.RGB = vbYellow
Case Else
.RGB = vbRed
End Select
End With
End Sub
Off topic, a little - but I was reminded of one of my first office jobs back in the days when adding machines had a big lever on the side and printed the result on a roll of paper.
It was my job to process sales orders and calculate commissions. On the wall was a big black peg board with all the salesmen’s names in a column on the left. Against each name were three elastic strings, red, green and yellow which showed the monthly, quarterly and annual performance against targets shown by vertical marks. I would spend an hour or so every day, after my calculations had been verified, stretching the strings along.
All this was so that the Sales Director could see at a glance how each individual and each branch was performing.