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.

Sub ChangeColor()

  Dim Shp As Shape
  
    Set Shp = ActiveSheet.Shapes(Application.Caller)
    
      Select Case Range("A1").Value
        Case Is < 5
          Clr = vbGreen
        Case Is = 5
          Clr = vbYellow
        Case Is > 5
          Clr = vbRed
      End Select
      
      Shp.Fill.ForeColor.RGB = Clr
      
End Sub

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