inserting an autoshape into a chart

[NG]

Hello everyone,

I have a smiley that changes color (yellow, green, red) based on a
percentage, I made that work with some help.

What i need is to insert that smiley face into a chart and have it change
colors when the new data is inserted into the next cell. I want to locate the
smiley on the upper right hand cornern of the chart.

I tried copy and paste, but it only pastes the current face and no color
change happens. I tried inserting it as a link but I cannot. How do i do
this???????

Thanks

 

[Luke M]

speculating here, I'm guessing you're using some type of worksheet_change
event to cause the color of your autoshape to change. In which case, you
would need to modify the coding to find the new autoshape on your chart and
change it's color as well.

We'd prb be able to provide greater assistance if you provided more detail
as to how you got the color changing to occur originally (ie paste macro, if
used)

 

[NG]

Luke M,

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Excel.Range
Dim myShape As Excel.Shape

Set r = Me.Range("$A1:$A99")
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("$A$1:$A$99")) Is Nothing Then Exit Sub

Set myShape = Me.Shapes("AutoShape 4")
If Target.Value > 90 Then
myShape.Fill.ForeColor.RGB = RGB(0, 255, 0)
ElseIf Target.Value > 85 Then
myShape.Fill.ForeColor.RGB = RGB(255, 255, 0)
Else
myShape.Fill.ForeColor.RGB = RGB(255, 0, 0)
End If
End Sub

Thank you

 

[Luke M]

Very good. First, make a note of the name of the shape on your chart. (when
you select the object, the name appears in box to the left of formula bar).
I'll go with the assumption that it's name is "AutoShape 5".

'In the coding, modify this line:
Set myShape = Me.Shapes("AutoShape 4")

'to the following (modifed as required)
Set myShape = Sheets("Name of Chart Sheet").Shapes("AutoShape 5")


The original line of coding defined the shape as being on the worksheet. The
new line defines the shape as being on a different sheet/chart.

 

[NG]

Luke,

I appologize for the dumb question but where does it state the name of the
chart sheet??????

 

[Luke M]

It'll be whatever name you have assigned the worksheet that you have your
chart on (in the XL workbook).

If it's the first chart, by default it has a name of Chart1, but you could
have changed it to something else.

 

[NG]

Luke,

This is what I entered:
Set myShape = Sheets("Chart 1").Shapes("AutoShape 1")

I get a Run-time error "9" Subscript out of range, when I enter the code

 

[Luke M]

Everything works fine on my machine...
Are you sure you entered the sheet and object name correctly? As in, is the
sheet really named "Chart 1" or "Chart1"?

In my workbook, shape's name is "AutoShape 1", on sheet "Chart 1"


'=====
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Excel.Range
Dim myShape As Excel.Shape

Set r = Me.Range("$A1:$A99")
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("$A$1:$A$99")) Is Nothing Then Exit Sub

Set myShape = Sheets("Chart 1").Shapes("AutoShape 1")
If Target.Value > 90 Then
myShape.Fill.ForeColor.RGB = RGB(0, 255, 0)
ElseIf Target.Value > 85 Then
myShape.Fill.ForeColor.RGB = RGB(255, 255, 0)
Else
myShape.Fill.ForeColor.RGB = RGB(255, 0, 0)
End If
End Sub

 

[NG]

Luke,

Can you send me the spreadsheet with the data at (e-mail address removed)

Thanks

NG

 

[NG]

Luke,

You dont have to email the spreadsheet to me, I got it to work.

Thank you