Autoshape in Chart controlled from a different worksheet

[PAUL_HARTE]

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

 

[PAUL_HARTE]

EXCELLENT!!!

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

cheers

Paul

 

[PAUL_HARTE]

EXCELLENT!!!

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

cheers

Paul