|
inserting an autoshape into a chart
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 |
inserting an autoshape into a chart
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) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NG" wrote: 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 |
inserting an autoshape into a chart
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" wrote: 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) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NG" wrote: 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 |
inserting an autoshape into a chart
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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NG" wrote: 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" wrote: 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) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NG" wrote: 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 |
inserting an autoshape into a chart
Luke,
I appologize for the dumb question but where does it state the name of the chart sheet?????? "Luke M" wrote: 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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NG" wrote: 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" wrote: 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) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NG" wrote: 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 |
inserting an autoshape into a chart
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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NG" wrote: Luke, I appologize for the dumb question but where does it state the name of the chart sheet?????? "Luke M" wrote: 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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NG" wrote: 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" wrote: 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) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NG" wrote: 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 |
inserting an autoshape into a chart
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" wrote: 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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NG" wrote: Luke, I appologize for the dumb question but where does it state the name of the chart sheet?????? "Luke M" wrote: 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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NG" wrote: 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" wrote: 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) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NG" wrote: 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 |
inserting an autoshape into a chart
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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NG" wrote: 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" wrote: 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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NG" wrote: Luke, I appologize for the dumb question but where does it state the name of the chart sheet?????? "Luke M" wrote: 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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NG" wrote: 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" wrote: 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) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NG" wrote: 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 |
inserting an autoshape into a chart
|
inserting an autoshape into a chart
Luke,
You dont have to email the spreadsheet to me, I got it to work. Thank you "NG" wrote: Luke, Can you send me the spreadsheet with the data at Thanks NG "Luke M" wrote: 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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NG" wrote: 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" wrote: 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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NG" wrote: Luke, I appologize for the dumb question but where does it state the name of the chart sheet?????? "Luke M" wrote: 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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NG" wrote: 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" wrote: 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) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NG" wrote: 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 |
| All times are GMT +1. The time now is 08:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com