Written by Debra Dalgleish from Contextures Blog
We all want to be remembered for our contributions to humanity. That’s why, way back in 2004, I made an Excel Smiley Face sample (aka “Happy Face”). There was a new version in 2009, and here’s another update, to change the Smiley Face shape colour automatically. You can do the same thing with other shapes too, like partial circle “pie slice”. Is there a Nobel Prize in this category?
Original Excel Smiley Face
I don’t remember what inspired my first Excel Smiley Face sample file. Maybe someone asked a question in the old Excel newsgroups, and that was my answer.
Anyway, the worksheet has a Smiley Face shape, and a cell where you can enter a whole number between 1 and 100.
Number Cell Data Validation
In cell H3, where the number is entered, there is a data validation rule that controls what can be typed in the cell.
As you can see in the screen shot below, you’re only allowed to enter a Whole number, and it must be between the Minimum of zero and the Maximum of 100.
Smiley Face Mouth Curve
If you click on the Smiley Face shape to select it, you’ll see handles on the shape’s border.
There’s also a yellow handle on the mouth, and you can drag that up or down, to change the mouth’s curve.
Record a Macro
If you record a macro while you select the shape and then drag that mouth handle, you’ll get code similar to this screen shot.
Change the Curve Automatically
In the Happy Face sample file, there is code similar to that, and it runs automatically when you type a number in cell H3.
There are 2 versions of the Happy Face code in the sample file, one for Excel 2003, and one for Excel 2007 and later.
I had to create that new code because the angle settings change in Excel 2007, but I’m not sure why. You can read more about that change to the code in the Smiley Face Chart Update article.
See the Mouth Curve Code
To see the Excel 2007 code, right-click Happy Face Gauge 2007 sheet tab, and click View Code.
There is a Worksheet_Change procedure, and it changes the mouth’s curve if the worksheet change was made in cell H3.
If Target.Address = "$H$3" Then Application.EnableEvents = False sh.Adjustments.Item(1) _ = myMin + (myMax - myMin) _ * Target.Value / 100 End If
Change the Face Colour
Recently, someone asked if it was possible to change the face colour too, along with the mouth curve.
Well, that sounded like fun, so I’ve made a new version of the Excel Smiley Face, and it also changes colour, based on the number in H3.
There are numbers on the worksheet, to explain when the colour changes, but that list is for reference only.
Revised Worksheet_Change Code
To make the colour change, I added a new section in the Worksheet_Change code.
There’s a new variable, myColor, and it’s based on the number in H3. The 90 and 60 are typed in that code, so you could change them there, if you want to set different limits.
'change shape colour 'less than 60% = red '60%- 90% Amber '90%-100% Green Select Case Target.Value Case Is >= 90: myColor _ = RGB(146, 208, 80) 'green Case Is >= 60: myColor _ = RGB(255, 192, 0) 'orange Case Else: myColor _ = RGB(255, 0, 0) 'red End Select sh.Fill.ForeColor.RGB = myColor
So, if the number in H3 is less than 60, the face changes to red.
Shape RGB Setting
To set the colour for the shape, I had to get the RGB settings for each colour:
- Green: RGB(146, 208, 80)
- Orange: RGB(255, 192, 0)
- Red: RGB(255, 0, 0)
If you’d like to use other colours, here’s how you can find their RGB settings:
- Right-click a cell, and click the arrow beside the Fill Color button
- Click More Colors
- Click a color on the Standard tab, then click the Custom tab
- For Color Model, choose RGB
- Make note of the Red, Green and Blue numbers
- Click Cancel, to close the window
Then, edit the Worksheet_Change code, to use your new RGB settings.
Other Types of Shapes
You could use the same technique to change the fill colour for different types of shapes – maybe your co-workers aren’t the “Happy Face” type!
There’s another sheet in the new Happy Face file, and it has a “Partial Circle” shape. Change the percentage in cell H3, and the “pie slice” changes size, and the colour changes too.
There are 4 colours for this shape, and those are shown on the worksheet.
Download the Sample Files
To see how the Smiley Faces work, you can download the workbooks from the Excel Sample Files page on my Contextures website.
The new Happy Face file is in the VBA section – UF0050 – Happy Face Gauge Colour
The original Happy Face file is in the Data Validation section – DV0018 – Happy Face Gauge.
Change Excel Shape Colour Automatically
Change Excel Shape Colour Automatically is a post from Contextures Blog and is not allowed to be copied to other sites