Change the Color of An Object in a Chart with VBA

[turner38]

I am trying to change the color of an Oval I have in a chart based on a cell I am referencing. I am trying to use the Oval as a quick indicator of performace. Here is the code I have so far:

Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("AK11")
If Target.Value = "Green" Then
Shapes("Oval 17").Fill.ForeColor.RGB = RGB(0, 255, 0)
End If
If Target.Value = "Yellow" Then
Shapes("Oval 17").Fill.ForeColor.RGB = RGB(255, 0, 0)
End If
If Target.Value = "Red" Then
Shapes("Oval 17").Fill.ForeColor.RGB = RGB(255, 255, 0)
End If

End Sub

When I run it I get a 'Run-time Error '-147024809 (80070057)' "The item with the specified name wasn't found."

Since my shape is apart of my chart (I can move it out of the chart area) do I need to reference it differently?

Any Help would be Awesome!

John

 

[repairman615]

Hello,

Welcome to the Message Board!!

This will fire everytime any cell in the sheet is changed:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><SPAN style="color:#007F00">'Set Target = Range("AK11")</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> UCase(Range("AK11").Value) = "GREEN" <SPAN style="color:#00007F">Then</SPAN><br>        Shapes("Oval 17").Fill.ForeColor.RGB = RGB(0, 255, 0)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> UCase(Range("AK11").Value) = "RED" <SPAN style="color:#00007F">Then</SPAN><br>        Shapes("Oval 17").Fill.ForeColor.RGB = RGB(255, 0, 0)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> UCase(Range("AK11").Value) = "YELLOW" <SPAN style="color:#00007F">Then</SPAN><br>        Shapes("Oval 17").Fill.ForeColor.RGB = RGB(255, 255, 0)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[turner38]

Hello Repairman,

Thanks for the code, but unfortunately I am still getting the same error. I think my issue is around how I am referencing the Oval in my chart. When I try the same code with an Oval that is not apart of my chart it works perfectly. Any idea what I can do to fix this?

Thanks,

John

 

[repairman615]

Hello,

I get the same error only when "Oval 17" does not exist on the same sheet or exist at all.

My first thoughts are that it is on the same sheet where this code is inserted (the sheet module right??).

Next, that the certianty of this name "Oval 17" is valid.

Will you double check these points.

-Jeff

 

[turner38]

The code is in the same sheet as well as the object is named "Oval 17". Its like I need to reference the chart then the shape itself. Isn't there a parent/child property relationship between a chart object and a chart?

 

[repairman615]

I noticed after I placed a oval over the chart, I couldn't move the oval out of the chart... in a way it became part of the chart. I learned something!!

Try:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><SPAN style="color:#007F00">'Set Target = Range("AK11")</SPAN><br>    <br>    <br>    <SPAN style="color:#00007F">If</SPAN> UCase(Range("AK11").Value) = "GREEN" <SPAN style="color:#00007F">Then</SPAN><br>        ActiveSheet.ChartObjects("Chart 1").Activate<br>        ActiveChart.Shapes("Oval 17").Fill.ForeColor.RGB = RGB(0, 255, 0)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> UCase(Range("AK11").Value) = "RED" <SPAN style="color:#00007F">Then</SPAN><br>        ActiveSheet.ChartObjects("Chart 1").Activate<br>        ActiveChart.Shapes("Oval 17").Fill.ForeColor.RGB = RGB(255, 0, 0)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> UCase(Range("AK11").Value) = "YELLOW" <SPAN style="color:#00007F">Then</SPAN><br>        ActiveSheet.ChartObjects("Chart 1").Activate<br>        ActiveChart.Shapes("Oval 17").Fill.ForeColor.RGB = RGB(255, 255, 0)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Change "Chart 1" to your chart name.

-Jeff

 

[repairman615]

Wow, just to clarify.

After clicking on a chart to activate, then when I drew an oval on top of chart, the oval became part of the chart. I could not move the oval outside the chart area. The second bit of code work for me in this case and I suppose you may not be able to move the oval outside of the chart.


on the other hand,

If a oval was drawn outside the chart and then moved inside the chart it was still 'free' to be moved out and the first code posted worked.

 

[turner38]

Repairman, you are the man! It works perfectly. Thanks for your help!

 

[repairman615]

You are welcome, I am glad to help.



Just some food for thought if I may,

Using a Worksheet Change Event, when a cell gets changed, that cell is the target.

Here is an example of select case that may also do:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <br>    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> (UCase(Range("AK11").Value))<br>    <br>        <SPAN style="color:#00007F">Case</SPAN> "GREEN"<br>            ActiveSheet.ChartObjects("Chart 1").Activate<br>            ActiveChart.Shapes("Oval 17").Fill.ForeColor.RGB = RGB(0, 255, 0)<br>        <SPAN style="color:#00007F">Case</SPAN> "RED"<br>            ActiveSheet.ChartObjects("Chart 1").Activate<br>            ActiveChart.Shapes("Oval 17").Fill.ForeColor.RGB = RGB(255, 0, 0)<br>        <SPAN style="color:#00007F">Case</SPAN> "YELLOW"<br>            ActiveSheet.ChartObjects("Chart 1").Activate<br>            ActiveChart.Shapes("Oval 17").Fill.ForeColor.RGB = RGB(255, 255, 0)<br>            <br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

-Jeff

 

[turner38]

Thanks for Select Case option. I like this as well since it is a more concise way of performing the task.