Spinning images

Basically this will be achieved through using names. Let’s start!

Assuming we have a file where we e. g. want to spin the flags of countries. This could be the case if you are creating dashboards (sign up for my newsletter if you don’t want to miss my upcoming “14 days to dashboard mastery”) and want to show the corresponding national flag to the country that is reported in the dashboard.

So I have downloaded the following two images from Inconfinder.net:

 

The next step we have to take is to define several ranges that we need. Altogether there will be four of them and below you can find the area and what they are needed for:

1. SpinCountry – Cell A1: This will be the linked cell of a later to be inserted spin button with which we will toggle between the images
2. DE – Cells A3:C14: This will hold our image of the German flag
3. UK – Cells E3:G14: This will hold our image of the English flag
4. CountryImage: This named area will NOT be linked to a cell but to a formula (we will come to this later).

So please set up the first three areas by selecting the cells mentioned above and enter the name left of the edit bar in the field that usually shows the cell reference.

See the screenshot below to see its position in Excel 2003 (2007 is very similar):

What we will do now is to insert the spin button and link it to the previously mentioned cell labelled “SpinCountry”. Do so by going to your developer ribbon then into the controls section and select the spin button  from the form controls submenu (click the little down arrow below the insert button)). Click and drag it until it has the size you want.

Right click this button and select  Format Control.

By the way in Excel 2003 this will be handled through the Control toolbox which will appear if tick the check mark in front of it by going to View – Toolbars

The Maximum value should be set to 2 and the Minimum value to 1, because we have only to images we want to scroll through. Enter the name of our previously created SpinCountry cell into the field cell link

The next step would be to insert the images into our spreadsheet document. Put your cursor to cell A3 and then select Insert – Picture – From File. Choose the German Flag which you have hopefully saved to your hard disk (if not, here it is again)

Make sure that it covers the area A3 to C14 (which we have previously defined with the name “DE”). You can make sure that it “snaps” to cell borders by holding down the ALT key on your keyboard and then using the little circular handles in the middle and on the edges of the image. This way, you are able to resize the image exactly to the size of the named range.

Okay, let’s proceed with the second image (and yes, here it is again):

Resize it as described above but this time it should cover the area named UK (cells E3 to G14).

Now comes our little “magic” trick: We will define the fourth and most important name of our table, CountryImage. But this time this is not an area but a formula.

I will describe this for MS Excel 2003 but the way for Excel 2007 is very similar: Go to Insert – Name – Define. In Name for the worksheet enter CountryImage and in refers to enter the following formula:

IF(SpinCountry=1,DE,UK)

Make sure that when using a non-European version you enter the formula with a comma, otherwise use the semicolon.

This formula tells Excel that if the SpinCountry button is set to 1 it should show the German flag, otherwise the English Flag.

Select the range A3 to C14 now by going there and selecting with the arrow keys and click the camera tool once to make a snapshot of the selected area. Change to a new sheet in your spreadsheet file (e. g. Sheet 2) and click once with your cursor which should look like a little crosshair. This places the snapshot of the selected range on the new sheet.

Into the formula toolbar you should see a reference to the current image (Sheet1!$A$3:$C$14) looking like this:

We will change this reference now to our name CountryImage so the formula toolbar looks like this:

Go back to Sheet1 and cut the spin button by right-clicking it and paste it onto Sheet2. Now right click the image and select Format Picture. Go to the Colors and Lines tab and set both (Fill and Line) to No Fill / No Line.

One final thing to do for displaying purposes is to get rid of the grid (yep, that's a little poem ). Do this for each sheet by selecting all cells (CTRL+A) and select white as a fill color.

If you click the spin button now back and forth this should result in a change of the image. Voilà that's it. Hope you like it.