embedded chart events, module confusion

Showard27 · Feb 10, 2014

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:

Code:

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:

Code:

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

and in a standard module:

Code:

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

pgc01 · Feb 10, 2014

Hi Simon

I don't see where you are creating the object.

I did a small test, keeping your names, just with the class module (MyClassModule) and a standard module (Module1)

In module Module1:

Code:

Option Explicit
Private MyChartWE As MyClassModule ' My Chart With Events

Sub InitialiseChart()

    Set MyChartWE = New MyClassModule
    Set MyChartWE.myChartClass = Worksheets("Standards").ChartObjects(2).Chart
End Sub


Sub MFChart(ByVal ElementID As Long)
    MsgBox "Hi, you selected element ID:" & ElementID & " in the chart!"
End Sub

In module MyClassModule:

Code:

Option Explicit

Public WithEvents myChartClass As Chart

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

Now run InitialiseChart and then select something in the chart (for ex. one series or the gridlines)

Please test

Showard27 · Feb 10, 2014

PGC

thanks for your help, and it now kind of works. however it is causing a crash. I will try and explain;

What is supposed to happen is everytime a datapoint is selected on Chart2, the pie chart (chart 1) on the same worksheet is updated with new derived values. This works the first time, but when you click another datapoint, the selection box seems to be mimicing the position of chart1 elements, but overlaid (offset) onto chart2. this then causes excel to crash.

I have checked through and am not as far as I can see getting any cross references, equally none of the routines actual select anything so focus does not change. I am now at a complete loss.

This is actually a simple version of what I really need to do (development simplicity), but what started off as simple has got me flumoxed.

Simon

Showard27 · Feb 11, 2014

OK so a little debugging experimentation later and it seems that my code is leaving a legacy selection of "chart 1" in memory, even though its not selected, merely the focus of a "with" routine eg:

Code:

With Worksheets("Standards").ChartObjects("Chart 1").Chart
    .SeriesCollection(1).Values = MFCount
    .HasTitle = True
    .ChartTitle.Text = TitleString
End With

MFCount is an array populaed elsewhere in code, and TitleString is similarly constructed elsewhere in code, but neither select anything. I can stop now stop excel crashing with;

Code:

sendkeys "{ESC}"

but thats stops the macro being intuitive to the user and limits my options in the way I can complete this development. Has anyone any idea what is going wrong here ??? please

Simon

pgc01 · Feb 11, 2014

Hi Simon

I tried the code you posted and did not notice any problem

Could you post some code that crashes, so that we can test?

Showard27 · Feb 12, 2014

probably easiest if i can drop the file on you, only contains sample data, do you have a way

Showard27 · Feb 12, 2014

This is the full module, the only other code is in the class module that captures the datapoint select event

Code:

Public OpsArray() As Integer
Public StaffArray() As Integer
Public AgeArray() As Integer
Public Xarray() As String
Public MFCount(1 To 2) As Integer
Public ThisWS As Worksheet
Public Ser
Public DataP
Public LastRow As Integer
Public LastAge As Integer
Public AgeIn As Integer
Public GenderKey(1 To 2) As String
Public AgeString As String
Public TitleString As String

Private MyChartWE As MyClassModule ' My Chart With Events
Sub InitialiseChart()
    
    Set MyChartWE = New MyClassModule
    Set MyChartWE.myChartClass = Worksheets("Standards").ChartObjects("Chart 2").Chart

End Sub
Sub AgePop()

With Worksheets("Standards")
    LastRow = .Range("B65536").End(xlUp).Row
        ReDim OpsArray(2 To LastRow + 1)
        ReDim StaffArray(2 To LastRow + 1)
        ReDim AgeArray(2 To LastRow + 1, 1 To 2)
        ReDim Xarray(2 To LastRow + 1)
            For b = 2 To LastRow
                AgeArray(b + 1, 1) = .Range("b" & b).Value
                AgeArray(b, 2) = .Range("b" & b).Value - 1
            Next b
                AgeArray(2, 1) = 0
                AgeArray(LastRow + 1, 2) = 100
End With
End Sub
Sub MFChart(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)

If ElementID = 3 Then

Erase MFCount

GenderKey(1) = "Male"
GenderKey(2) = "Female"

Select Case Arg1
    Case Is = 1
    Set ThisWS = Worksheets("Ops")
    TString = "Ops"
    Case Is = 2
    Set ThisWS = Worksheets("Staff")
    TString = "Staff"
    End Select
    
Application.Run "AgePop"

Select Case Arg2

    Case Is = -1
        With ThisWS
            LastAge = .Range("A65536").End(xlUp).Row
                For i = 2 To LastAge
                    Select Case .Range("C" & i).Value
                                Case Is = "M"
                                    MFCount(1) = MFCount(1) + 1
                                Case Is = "F"
                                    MFCount(2) = MFCount(2) + 1
                                End Select
                Next i
        End With
    
TitleString = TString & " Only"
    
    Case Is >= 0
    
        With ThisWS
            LastAge = .Range("A65536").End(xlUp).Row
                For i = 2 To LastAge
                    AgeIn = DateDiff("d", .Range("B" & i).Value, Date) / 365
                        If AgeIn < AgeArray(Arg2 + 1, 2) And AgeIn > AgeArray(Arg2 + 1, 1) Then
                            Select Case .Range("C" & i).Value
                                Case Is = "M"
                                    MFCount(1) = MFCount(1) + 1
                                Case Is = "F"
                                    MFCount(2) = MFCount(2) + 1
                                End Select
                        Else
                        End If
                Next i
        End With

TitleString = TString & " " & AgeArray(Arg2 + 1, 1) & " to " & AgeArray(Arg2 + 1, 2)
'SendKeys "{ESC}"
End Select
Application.Run "GenderChart"

Else: End If

End Sub
Sub GenderChart()

With Worksheets("Standards").ChartObjects("Chart 1").Chart
    .SeriesCollection(1).Values = MFCount
    .SeriesCollection(1).XValues = GenderKey
    .ChartTitle.Text = TitleString
End With

End Sub

Simon

pgc01 · Feb 12, 2014

Hi Simon

I'll try to look into it tonight (GMT) if it's not solved in the meantime.

Showard27 · Feb 12, 2014

Many thanks

pgc01 · Feb 12, 2014

Hi Simon

Sorry, I don't have time to debug your code, so I read with attention your post #3, where you explain what you want and that it crashes and tried to do a very simple example, that I hope it similar and that worked without problem.

Since this is a very simple test, I hope it will help in understanding the problem.

This is what I did:

In worksheet "Standards", created 2 charts:

- created a pie chart with 3 values, "Chart 1". Added the data labels to see the value of each slice (so that I see them changing during the test).
- created a column clustered chart with 2 values, "Chart 2"

From what I understood you want to select a datapoint in "Chart 2" and that triggers a change in the values of "Chart 1"

In this simple example,
- if you select the first column (first datapoint) in "Chart 2" then the code adds 1 to all the values in "Chart 1"
- if you select the second column (second datapoint) in "Chart 2" then the code subtracts 1 to all the values in "Chart 1"

This is a simple example but seems to me similar to what you want.

The test I did was something like:
1- ran InitialiseChart()
2- did some selections
- selected a datapoint in "Chart 2" and saw the values in the pie chart slices change
- selected a cell
- selected again one of the datapoints in "Chart 2" and saw the values in the pie chart slices change
- selected in "Chart 2" column 1 (point 1) and then column 2, and then again column 1 (did this several times in sequence) and saw the values in the slices in "Chart 1" change with no problem.

Hope I understood well what you need and this helps.

In class module

Code:

Option Explicit

Public WithEvents myChartClass As Chart

Private Sub mychartclass_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
      MFChart ElementID, Arg1, Arg2
End Sub

in Module1:

Code:

Option Explicit

Private MyChartWE As MyClassModule ' My Chart With Events

Sub InitialiseChart()

    Set MyChartWE = New MyClassModule
    Set MyChartWE.myChartClass = Worksheets("Standards").ChartObjects("Chart 2").Chart
End Sub


Sub MFChart(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
Dim cht As Chart
Dim j As Long
Dim vArr As Variant

' only if a data point is selected in series 1
If ElementID <> xlSeries Then Exit Sub
If Arg1 <> 1 Then Exit Sub
If Arg2 = -1 Then Exit Sub

' add or subtract 1 to "Chart 1" series 1 values depending on selection of first or second datapoint in "Chart 2"

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

With cht.SeriesCollection(1)
    vArr = .Values
    For j = 1 To .Points.Count
        vArr(j) = vArr(j) + IIf(Arg2 = 1, 1, -1)
    Next j
    .Values = vArr
End With

End Sub

embedded chart events, module confusion

Showard27

Board Regular

Excel Facts

pgc01

MrExcel MVP

Showard27

Board Regular

Showard27

Board Regular

pgc01

MrExcel MVP

Showard27

Board Regular

Showard27

Board Regular

pgc01

MrExcel MVP

Showard27

Board Regular

pgc01

MrExcel MVP

Similar threads

Forum statistics

Share this page

embedded chart events, module confusion

Board Regular

Excel Facts

MrExcel MVP

Board Regular

Board Regular

MrExcel MVP

Board Regular

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock