VBA Drawing Objects

I want to use VBA to update a drawing object's color, such as a circle.

For example, pretend I am counting sheep and I need to know when I have reached the maximum count by the visual aide of a stoplight. The script in my head reads: If the max. count of the sheep is less than 5, then the stoplight is green. If the max. count of the sheep is equal to 5, then the stoplight is yellow. If the max. count of the sheep is greater than 5, then the stoplight is red.

Is there a way to do such a thing on excel and with VBA?

I greatly appreciate all of your time and help!
Thanks!

Welcome to the forum.

The best place to start is with the macro recorder, and then adapt from there.

Here is a nice method using formulas without VBA. It does involve a bit more legwork. Though, if you are not adept at creating code, it may be easier.

You have three conditions that will be graphically represented by three pictures.

1. Open a new workbook with two worksheets. Sheet1 and Sheet2.
   
2. In Sheet2, range A1, draw out your picture that represents the <5 condition.
3. In Sheet2, range B1, draw out your picture that represents the =5 condition.
4. In Sheet2, range C1, draw out your picture that represents the >5 condition.
   
5. Each picture should be sized to the same dimensions as the range that contains it.
   
6. Right click any one picture in Sheet2 and copy it.
   
7. Go to Sheet1 and paste it.
   
8. Position it and resize it to your liking.
   
9. As this picture is selected, enter "=ChoosePic", without the quotes, into the formula bar. Confirm with enter.
   
10. Add a workbook name that will point to one of the three ranges in Sheet2.
    
11. Insert, Name, Define, ChoosePic, refers to:
12. =IF(Sheet1!$A$5<5,INDIRECT("Sheet2!A1"),IF(Sheet1!$A$5=5,INDIRECT("Sheet2!B1"),INDIRECT("Sheet2!C1")))
    
13. This formula is evaluating the value in cell 5 of Sheet1. Of course you can change this and make it more flexible by naming the cell that contains your value.
    
14. Download the example that is produced by following the above instructions.

Example Workbook in Zip File

Hello moquenma,

Welcome to the Forum!

Here is how to do it in VBA. After you have installed this macro, you can attach it to your shape by Right Clicking the drawing object and selecting Assign Macro.... Choose ChangeColor from the the dialog box and click OK. The control cell is A1. You can change this to any cell you want. It is marked in red. When you click the shape the color will change based on the cell's value.

Please Login or Register  to view this content.

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

Sincerely,
Leith Ross

Wow! Thank you for your replies and wonderful examples!

Is there a way I can link the code on excel to a picture in powerpoint?

Again, I greatly appreciate your help.

Hello moquenma,

What type of picture did you want to link to in PowerPoint? What did you want to happen once it is linked?

Sincerely,
Leith Ross

Hi Leith,

My picture is a colored circle. Once it's linked I would like it to be automatically updated. For example, if we use that sheep counting example again, if I enter the number 3 in my excel data sheet, I would like the circle in powerpoint to become green automatically.

There is a wonderful stoplight example in this thread that another member created in excel. I would like to emulate that example and have the updates appear on my powerpoint.

Thank you so much for your time and help!

mo

One solution that might work is to have an excel sheet in the powerpoint that controls my circle, but the excel sheet in the ppt can be controlled by an outside excel sheet.

I think that would work, but if you have any other solutions, I would greatly appreciate your input! Thanks everyone!

I run the VBA code above but get an error on the

Shp.Fill.ForeColor.RGC = Clr

I am a real noob at this, so any assistance would be appreciated.

Thank you.

That should have read

Shp.Fill.ForeColor.RGB = Clr