embedded chart events, module confusion

[Showard27]

Hi all

I am confusing myself over where code needs to sit, and standard versus class modules.

I have an embedded bar chart, that needs to run a macro when any of the bars are selected. when this was a chart sheet it worked fine, but I can seem to get it working as an embedded chart.

Currently my class module looks like this:

Public WithEvents mychartclass As Chart

Private Sub mychartclass_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
      Application.Run "MFChart"
      MsgBox "Success"
End Sub

In the Thisworkbook code:

Private Sub Workbook_Open()
      Application.Run "InitialiseChart"
End Sub

and in a standard module:

Sub InitialiseChart()
    Set myClassModule.mychartclass = Worksheets("Standards").ChartObjects(2).Chart
End Sub

what am i doing wrong, have i got something in the wrong place ?

Many thanks for any help or guidance. Excel 2003 by the way.

Simon

 

[Showard27]

Thanks for all your help. what you have created exactly mimics what I want, as the main calculation that creates the values for the pie chart works fine. Essentially I have now stripped out all array building routines, and have a simple sub that mimics what yours does. this looks like this:

Sub GenderChart()

Dim cht As Chart

Set cht = Worksheets("Standards").ChartObjects("Chart 1").Chart

With cht
    .SeriesCollection(1).Values = MFCount
    .SeriesCollection(1).XValues = GenderKey
    .ChartTitle.Text = TitleString
End With

End Sub

everything runs to the .SeriesCollection(1).Values line, which now I have stepped through the code again, is where the problem emerges.

Immediately before this line runs, the worksheet where the charts reside looks exactly as you would expect, 1 bar chart and 1 pie chart. however after running this line, a second copy of the pie chart appears, overlaying the bar chart, and any selection event after that selects the copy. if you then click ouside the chart areas, excel crashes.

completely driven to distraction now, and am starting to suspect an excel error with legacy information somehow floating around in sript.

 

[pgc01]

Can you do a small test?

With the rest of the code you used for this last test, change the GenderChart() routine to feed the values with a simple constant array.

This will simply toggle the values. (1,2,3) to (2,3,4) and back.

Is it simple to include this in your testing?

Sub GenderChart()
Static b As Boolean
Dim cht As Chart

Set cht = Worksheets("Standards").ChartObjects("Chart 1").Chart

b = Not b
With cht
    .SeriesCollection(1).Values = IIf(b, Array(1, 2, 3), Array(2, 3, 4))
    .SeriesCollection(1).XValues = Array("A", "B", "C")
End With

End Sub

 

[Showard27]

Hi

Fortunately I have moved all the calcs to a different sub, so this was an easy one to substitute and test.

If nothing else its proven that its not the data thats killing this, because exactly the same thing happens as previous, a ghost copy of the pie chart appears overlaying the bar chart when I step through the proceedure, and screws any selections done after the first pass. There is something legacy hanging around in the script of this file, that creating and giving focus to a ghost creation.

 

[Showard27]

Just out of interest, I have copied the data to a virgin file, and "copy pasted" all the code cleanly to that file also, just in case there was some legacy development stuff floating around in the workbook. Same happens.....losing the will to live

 

[pgc01]

Just out of interest, I have copied the data to a virgin file, and "copy pasted" all the code cleanly to that file also, just in case there was some legacy development stuff floating around in the workbook. Same happens.....losing the will to live

That's also what I would have done. I'm sorry it didn't work.
I don't see how this can be a problem of this code that deals with the charts, but if you can add something to the code I posted that reproduces the problem I'd be happy to look into it.

 

[Showard27]

Thanks again for all your help. Again by way of elimination I have just tried the exact same file on Excel 2010 install, rather than the corp 2003 install. Guess what it worked perfectly. So I am now stuck with finding a completely different approach to the problem as all my users are on 2003.

Oh well back to the drawing board

Simon

 

[pgc01]

That is good to know. The upside is that this means that you can trust the logic of the solution. The downside is, of course, that it does not work nor does it tell you what you have to do to fix it.

Before throwing everything away, you could try the same thing with another event. If, as you say, the problem is in excel itself, who knows if it's not just an error in the Selection event code.

I was thinking about the MouseUp Event. You could try it. If when the mouse goes up it's over a datapoint, execute your code.

 

[Showard27]

OK, just tried that on a very simple msgbox routine, inside the class event, unfortunatly it does return the ElementID or the Arg's so I have no idea which datapoint it is over.

Simon

 

[pgc01]

You are right in that the MouseUp event does not return those values directly, but it returns the XY coordinates of the point.

You can use the GetChartElement() method of the chart object. It has as input the XY coordinates and returns ElementID, Arg1, Arg2. This allows you to determine if when the mouse goes up it's over a datapoint.

 

[Showard27]

Hi

I think I am at the give up stage. Spent the evening learning and experimenting with GetChartElement(), got it working at a simple MsgBox level, but once I started to pass any info to the other chart exactly the same happens, I get a ghost selection on the screen which kills the process or excel depending how far I take it. Do you think this is part of my install, or an issue with the 2003 version ?