Autoshape in Chart controlled from a different worksheet

[[email protected]]

Hi all,

Can somebody please help.. i have a Chart (called Chart2 - on its own
page) and i have the data (sheet1) on another page.... i am trying to
get the autoshape in Chart2 to change colour based on the data from a
cell on Sheet1.

i have the following code....

i can get the shape to change colours on Sheet1, but, when i try it on
Chart2 nothing happens.

NB: this code is in Sheet1

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myColor As Long
Dim myShape As Shape

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("G119")) Is Nothing Then Exit Sub

Set myShape = Worksheets("Chart2").Shapes("Oval 1035")

Select Case LCase(Target.Value)
Case Is > "1": myColor = 53
Case Is < "1": myColor = 33
Case Is = "1": myColor = 25
Case Else
myColor = 0
End Select

If myColor = 0 Then
myShape.Fill.Visible = False

Else
With myShape.Fill
.Visible = True
.ForeColor.SchemeColor = myColor
End With
End If


End Sub

Any help WHAT so ever would be greatly appreciated...

Many thanks

Paul

[Andy Pope]

Hi Paul,

The reason it worked on a Worksheet was that you had an explicit
reference to the Worksheets object even though you where using the
chartsheets name. See comment in code.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myColor As Long
Dim myShape As Shape

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("G119")) Is Nothing Then Exit Sub

' chart object not worksheet
Set myShape = Charts("Chart2").Shapes("Oval 1035")

' non textual comparison
Select Case Target.Value
Case Is > 1: myColor = 53
Case Is < 1: myColor = 33
Case Is = 1: myColor = 25
Case Else
myColor = 0
End Select
If myColor = 0 Then
myShape.Fill.Visible = False

Else
With myShape.Fill
.Visible = True
.ForeColor.SchemeColor = myColor
End With
End If

End Sub

Cheers
Andy

[email protected] wrote:
> Hi all,
>
> Can somebody please help.. i have a Chart (called Chart2 - on its own
> page) and i have the data (sheet1) on another page.... i am trying to
> get the autoshape in Chart2 to change colour based on the data from a
> cell on Sheet1.
>
> i have the following code....
>
> i can get the shape to change colours on Sheet1, but, when i try it on
> Chart2 nothing happens.
>
> NB: this code is in Sheet1
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim myColor As Long
> Dim myShape As Shape
>
> If Target.Cells.Count > 1 Then Exit Sub
> If Intersect(Target, Me.Range("G119")) Is Nothing Then Exit Sub
>
> Set myShape = Worksheets("Chart2").Shapes("Oval 1035")
>
> Select Case LCase(Target.Value)
> Case Is > "1": myColor = 53
> Case Is < "1": myColor = 33
> Case Is = "1": myColor = 25
> Case Else
> myColor = 0
> End Select
>
> If myColor = 0 Then
> myShape.Fill.Visible = False
>
> Else
> With myShape.Fill
> .Visible = True
> .ForeColor.SchemeColor = myColor
> End With
> End If
>
>
> End Sub
>
> Any help WHAT so ever would be greatly appreciated...
>
> Many thanks
>
> Paul
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

[[email protected]]

EXCELLENT!!!

Thats exactly what i needed... many many many thanks....

cheers

Paul

[[email protected]]

EXCELLENT!!!

Thats exactly what i needed... many many many thanks....

cheers

Paul