The Excel Smiley Face Chart Revisited

A few years ago, I added a sample workbook on my site, that features an adjustable Smiley Face Chart. Type a number between 0 and 100, and the mouth changes from a smile to a frown, based on the score.

HappyFace01

The Score cell, H3, has data validation, to ensure that users enter a valid number. Below, you can see the data validation settings that are used in the Score cell.

Excel Data Validation settings

When you change the value in the Score cell, the Worksheet Change event code runs, and changes the curve of the mouth. You can see the code in the next section of this article.

A couple of years ago, John Walkenbach announced his scientific breakthrough in Smiley Chart development, using a scatter chart to create the smile. However, I remained loyal to my version though, and it has served me well over the years.

Using the Smiley Chart in Excel 2007

The Smiley Face chart is still very popular, and is downloaded hundreds of times every month. So, I opened it in Excel 2007 recently, and was surprised to find that it didn’t work correctly. I changed the score, and nothing happened.

First, I made sure that I had enabled macros, because the event code needs to run when the code is changed. Sometimes it’s easy to overlook that Security warning, just above the Formula bar.

Macros were enabled, so that wasn’t the problem. Eventually, I figured out that the degree settings for the smile are different in Excel 2007, so I added a new page to the sample workbook, using those settings.

Why did the setting change? I have no idea. Anyway, here’s the code, showing the minimum and maximum settings, for both Excel 2007 and Excel 2003.

HappyFace02

Download the Smiley Chart Sample File

You can download the zipped sample file with both the Excel 2003 and the Excel 2007 Smiley Chart version included.

__________

You may also like...

4 Responses

  1. Wonderful Excel blog « Earththing says:

    […] Wonderful Excel blog Do you use Excel to manipulate of visualize data? Then her is a blog that you may find interesting: Contextures Blog. I heard about it a couple of days ago, I think it was from the Tableau blog. This blog shows you all sorts of tips and tricks using Excel. Some of which require some VBA coding of macros, such as the Smiley Face Chart. […]

  2. Jack says:

    Is there a way to adjust shapes VBA compatibilty with Excel 2010??

  3. sara says:

    hi,
    Can anybody help me with excel
    I want to make excel worksheet with charts,graphs and pie and place a smile on the sheet to show result of good,bad and anxiety.
    I want to link the outcome of profit.e,g..0 to 50 dollars =bad 51 to 74 dollars=anxity 75 to 100 dollars good and automatically
    smiley appear on sheet while these ranges are made.

    thanks

    sara

Leave a Reply to Jack Cancel reply

Your email address will not be published. Required fields are marked *