Excel 2000 - Pictures instead of bars

[ff_emt1]

I have done some searching and have not found an answer to this problem.

First, I have Excel 2000. If it can be done in a later version and not 2000 there is a slim chance I can get upgraded.

Create a graph with 2 data points. I can insert a picture today, but they are the same picture, just a tall one and a short one.

Here is what I want to do. Create a graph with 2 thermometers, or maybe hour glasses, both the same size. The background empty picture is static, just the mercury or sand will change. The first one shows last years results as a percentage, 100% and the thermometer is full. The second one will display a year to date rising total. Thermometer increases the mercury level, the hour glass will decrease the sand in the top and increase the sand in the bottom.

If that is possible, how about this. Somehow use the Conditional Format command to change the color, blue for within goal range, red if over.

Any ideas?

If not in Excel, any Excel add-in you are aware of that might help.

Thanks ahead of time for your input.

Bob
Quality Facilitator
Kendallville, Indiana

 

[tlowry]

This turned out to be interesting.

Here is what I came up with:

Two images one red one green, but otherwise the same (i.e. size)
Red was Picture 5
Green was Picture 4

Put the one that is going to change in size on top of the other

Yours will be different names, to get the names I recoded a macro moving them and then copied the “ActiveSheet.Shapes("Picture 5")” and ActiveSheet.Shapes("Picture 4") for the macro below.

You need to get the percent accomplished into Range("picPerCent"). I used a slider control on the worksheet.

So…

Moving the slider changed the percentage in Range("picPerCent"), and then called the sub below making the red width change.

This is a start, you have to work on adding goals (perhaps a 3^rd image only visible after goal is passed.)

‘-------------------------------------------------
‘-------------------------------------------------
‘-------------------------------------------------
Option Explicit

Sub SetSize()
Dim ipercent As Integer
ipercent = Range("picPerCent").Value
If ipercent < 0 Or ipercent > 100 Then Exit Sub
ActiveSheet.Shapes("Picture 5").LockAspectRatio = msoFalse
ActiveSheet.Shapes("Picture 5").Width = _
ActiveSheet.Shapes("Picture 4").Width * ipercent / 100
'change height for vertical image
End Sub
‘-------------------------------------------------
‘-------------------------------------------------
‘-------------------------------------------------