Hi all,
I'm having problems selecting / activating a chart that is added via code.
The chart is actually copied and pasted from another worksheet to prevent the whole thing having to be redrawn from scratch and the code to do this is in a class module 'chartwithevents' that then allows the added chart to respond to user clicks..
What I have so far is:
In the main code..
Global myChart As New chartWithEvents
In the chartWithEvents class module:
Private pTargetSheet As Worksheet
Public Function addNewChart(targetWorksheet As Worksheet)
Set pTargetSheet = targetWorksheet
While targetWorksheet.ChartObjects.Count <> 0 'delete any existing charts on the worksheet
targetWorksheet.ChartObjects(1).Delete
Wend
Worksheets("HIDDENDATA").ChartObjects("ChartTemplate").Copy 'copy and paste chart from template
pTargetSheet.Paste Destination:=pTargetSheet.Range("B2")
'add data to graph etc etc
pTargetSheet.ChartObjects("ChartTemplate").Activate
End Function
Private Sub pChart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
'evaluate what was clicked and run appropriate code
Call addNewChart(pTargetSheet)
End If
End Sub
All works fine up to a point.. the chart is copied and pasted as expected and the data all populates fine however after the code has executed the chart remains unselected. This is annoying as it means that to continue working with the chart and have it respond to mouse events the user then has to click the chart again.. (this is more annoying than it sounds as clickin usually seslects various bits of the chart which then get dragged around etc!).
I've run the code in debug mode and also added a debug.print to the activate handler of the chart just to check and it appears the chart DOES get activated however as soon as the function exits the chart gets deactivated..
.. or more corectly the worksheet gets activated and in particular the cell that was directly behind the point on the chart that was clicked..
This is driving me nuts!
Any ideas?
Further info..
I added code to a button:
mysheet.ChartObjects("ChartTemplate").Activate
which is essentially the identical code to that in the class and it works fine.. I tried using the named sheet instead of pTargetSheet inside the class module but without success.
Bookmarks