How to deal with two charts and their events

[hrjohnson]

Hello,
I am working on an adapted version of the MouseMove Interactive chart (Interactive Chart in VBA using Mouse Move Event | Excel & VBA – Databison).
My goal is to have 5 seperate charts embedded on one worksheet that all have the hover capability. I have sucessfully got one chart to use the hover application in a worksheet, and now I have added a second chart and am trying to implement the same code. The first chart, the hover still works, but the second chart nothing happens. This is what I have in my code:

Under Sheet1 :

 Dim MyChart As New Class1


Private Sub  Worksheet_Activate()
Set MyChart.Ch = ActiveSheet.ChartObjects("Chart  1").Chart
Set MyChart.Ch2 = ActiveSheet.ChartObjects("Chart  122").Chart
End Sub

In Class1

Public  WithEvents Ch As Chart
Public WithEvents Ch2 As Chart
'Dim ElementID As  Long
'Dim Arg1 As Long
'Dim Arg2 As Long

Private Sub  Ch_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y  As Long)
Dim ElementID As Long
Dim Arg1 As Long
Dim Arg2 As Long
Dim  chart_data As Variant
Dim chart_label As Variant
Dim last_bar As  Long
Dim chrt As Chart
Dim ser As Series
Dim n As Integer, row_log As  Integer

On Error Resume Next

Ch.GetChartElement x, y, ElementID,  Arg1, Arg2

Set chrt = ActiveChart
Set ser =  ActiveChart.SeriesCollection(1)
chart_data = ser.Values
chart_label =  ser.XValues

Set txtbox = ActiveSheet.Shapes("hover")

If ElementID  = xlSeries Then
If Err.Number Then
'Find the corresponding log
For n =  4 To 90
If ActiveChart.SeriesCollection(Arg1).Points(Arg2).Parent.Name =  Worksheets("Log_comp").Cells(n, 1) Then
row_log = n
End If
Next  n

Set txtbox = ActiveSheet.Shapes.AddTextbox  _
(msoTextOrientationHorizontal, x + 200, y - 300, 250, 180)
txtbox.Name =  "hover"
txtbox.Fill.Solid
txtbox.Fill.ForeColor.SchemeColor =  9
txtbox.Line.DashStyle = msoLineSolid
txtbox.TextFrame.Characters.Text =  Worksheets("Log_comp").Cells(row_log, 2)

last_bar = Arg2
End  If
ser.Points(Arg2).Interior.ColorIndex = 44
txtbox.Left = x +  200
txtbox.Top = y -  300

Else
txtbox.Delete
ser.Interior.ColorIndex = 16
End  If
End Sub
Private Sub Ch2_MouseMove(ByVal Button As Long, ByVal Shift As  Long, ByVal x As Long, ByVal y As Long)
Dim ElementID As Long
Dim Arg1 As  Long
Dim Arg2 As Long
Dim chart_data As Variant
Dim chart_label As  Variant
Dim last_bar As Long
Dim chrt As Chart
Dim ser As Series
Dim  n As Integer, row_log As Integer

On Error Resume  Next

Ch2.GetChartElement x, y, ElementID, Arg1, Arg2

Set chrt =  ActiveChart
Set ser = ActiveChart.SeriesCollection(1)
chart_data =  ser.Values
chart_label = ser.XValues

Set txtbox =  ActiveSheet.Shapes("hoverOroville")

If ElementID = xlSeries Then
If  Err.Number Then
'Find the corresponding log
For n = 4 To 90
If  ActiveChart.SeriesCollection(Arg1).Points(Arg2).Parent.Name =  Worksheets("Log_comp").Cells(n, 1) Then
row_log = n
End If
Next  n

Set txtbox = ActiveSheet.Shapes.AddTextbox  _
(msoTextOrientationHorizontal, x + 200, y - 800, 250, 180)
txtbox.Name =  "hoverOroville"
txtbox.Fill.Solid
txtbox.Fill.ForeColor.SchemeColor =  9
txtbox.Line.DashStyle = msoLineSolid
txtbox.TextFrame.Characters.Text =  Worksheets("Log_comp").Cells(row_log, 2)

last_bar = Arg2
End  If
ser.Points(Arg2).Interior.ColorIndex = 44
txtbox.Left = x +  200
txtbox.Top = y -  800

Else
txtbox.Delete
ser.Interior.ColorIndex = 16
End  If

End Sub

I also numerous module codes, but they don't use events, just calculations pertaining to other parts of the workbook.
Thank you for the help! Also if you have any additional information on events, then that would be helpful too!

 

[Jon Peltier]

You need to set up a collection or array of instances of the class, one for each chart.

I wrote a tutorial some years ago on chart events, which is on a site that went black last year. Fortunately the Wayback Machine has it archived, at Chart Events in Microsoft Excel.

I also have a rather old sample workbook that shows how to instantiate multiple embedded charts, called Get Information From Embedded Charts (zip).

 

[hrjohnson]

Thank you for your reply, the document and example code are a big help.

Now it looks like that in your example code, each chart reacts to that chart event (of clicking on the graphs). Now in my class module, what do I have to add in order to make sure both graphs works correctly? Like is EvtChart_MouseMove work for all charts on the page, or do I need to write multiple MouseMove events like I was doing before? Or do I call the Chart like "Public WithEvents EvtChart() As Chart"?

 

[Jon Peltier]

You need only one class module, which responds to events in EvtChart.

When you set up an array or collection, it is a group of instances of the class. Each class only has one event-enabled chart assigned to it. Through the loop in the Set_All_Charts routine, each chart is assigned to its own unique instance of the class.

Option Explicit

Dim mycharts() As New clsChartEvent

Sub Set_All_Charts()
    If ActiveSheet.ChartObjects.Count > 0 Then
        ReDim mycharts(1 To ActiveSheet.ChartObjects.Count)
        Dim chtObj As ChartObject
        Dim chtnum As Integer
    
        chtnum = 1

        For Each chtObj In ActiveSheet.ChartObjects
            Set mycharts(chtnum).EmbChart = chtObj.Chart
            chtnum = chtnum + 1
        Next ' chtObj
    End If
End Sub

mycharts() is the array, declared as "New clsChtEvent", which means it's an instance of the class clsChtEvent ("New" means it's created whenever it is first cited in the code). It is redimensioned to the number of charts in the activesheet.

The event-enabled chart (what you're calling EvtChart, what my code calls EmbChart) links each instance of the class to each chart in the loop. This linkage is unique.

When you click on a chart, the chart's instance of the class is invoked. It uses the same code as all the other instances, but runs the code on the particular instance of the class, that is, on the appropriate EmbChart.

 

[hrjohnson]

Okay thank your for the help! The chart events are working now! Now I have another question:

In my MouseMove Event code, I create a textbox with specific text when it hovers over a certain point. Before (when I had a chart sheet) the textbox would hover in relation to where the point was (or x,y coordinates. But it that the first chart's textboxes are close to the points, but the second chart's textboxes are also being placed next to the first chart's points.
This is my code:

Private Sub EvtChart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
 Dim ElementID As Long
 Dim Arg1 As Long
 Dim Arg2 As Long
 Dim chart_data As Variant
 Dim chart_label As Variant
 Dim last_bar As Long
 Dim chrt As Chart
 Dim ser As Series
 Dim txtbox As Shape
 Dim n As Integer, row_log As Integer
     
On Error Resume Next
 
 
 With EvtChart
    .GetChartElement x, y, ElementID, Arg1, Arg2
    
     
    Set chrt = ActiveChart
     Set ser = ActiveChart.SeriesCollection(1)
     chart_data = ser.Values
     chart_label = ser.XValues
     
    Set txtbox = ActiveSheet.Shapes("hover")
     
    If ElementID = xlSeries Then
         If Err.Number Then
             'Find the corresponding log
                For n = 4 To 90
                    If ActiveChart.SeriesCollection(Arg1).Points(Arg2).Parent.Name = Worksheets("Log_comp").Cells(n, 1) Then
                        row_log = n
                    End If
                Next n
              'MsgBox "test"
             Set txtbox = ActiveSheet.Shapes.AddTextbox _
                                             (msoTextOrientationHorizontal, x + 200, y - 300, 250, 180)
    
             txtbox.Name = "hover"
             txtbox.Fill.Solid
             txtbox.Fill.ForeColor.SchemeColor = 9
             txtbox.Line.DashStyle = msoLineSolid
             txtbox.TextFrame.Characters.Text = Worksheets("Log_comp").Cells(row_log, 2)
             
             last_bar = Arg2
         End If
         ser.Points(Arg2).Interior.ColorIndex = 44
         txtbox.Left = x + 200
         txtbox.Top = y - 300
         
    Else
         txtbox.Delete
         ser.Interior.ColorIndex = 16
     End If
     
     End With
 End Sub

Are the x, y values related to the Evtchart object, or is it getting the x,y from somewhere else?

 

[Jon Peltier]

First of all, EvtChart is the chart that is responding to the events. You don't need to reference the active chart. The first few lines of the routine should look like:

With EvtChart
    .GetChartElement x, y, ElementID, Arg1, Arg2
    
     Set ser = .SeriesCollection(1)
     chart_data = ser.Values
     chart_label = ser.XValues
     
    Set txtbox = .Shapes("hover")

etc. And anywhere else you have ActiveChart, remove it and just leave the dot after ActiveChart. You probably also want to replace

Set ser = ActiveChart.SeriesCollection(1)

with

Set ser = .SeriesCollection(Arg1)

Cleaning up the code might be all you need, I can't tell.

You don't need to do anything directly with x and y. When the mouse move event fires, the event code starts, and the arguments button, shift, x, and y are automatically figured out and passed into the procedure. In other words, x and y are where the mouse pointer set off the event event. GetChartElement inputs this x and y, and outputs the ElementID, Arg1, and Arg2 for whatever chart element is beneath the mouse pointer.

 

[hrjohnson]

Oh okay, i think i see what is happening here. The x,y coordinates are the relational to the chart itself right? So if the textbox is created in the ActiveSheet, its using those point coordinates and adding a certain number to it. And since right now I am using two identical graphs, the textbox placement is in the same place. Is there a way to get the x,y coordinates of activesheet itself and place the textbox that way?

 

[Jon Peltier]

Put the textbox in the chart itself.

Set txtbox = EvtChart.Shapes.AddTextbox _
    (msoTextOrientationHorizontal, x + 200, y - 300, 250, 180)

I hadn't noticed that you were trying to line up a textbox outside the chart with elements within the chart.

 

[hrjohnson]

Okay so that definitely helps with my problem before, but now the chart is having trouble deleting the textboxes after the mouse has left the series point.

 

[Jon Peltier]

Each time you run the procedure, any declared variables in it are reinitialized. This means that the textbox variable you assign to a shape one time you move the mouse is forgotten the next time you move the mouse.

Move "Dim txtbox As Shape" out of the mouse move procedure into the declarations section of the class module, between Option Explicit* and the first procedure.

*Don't see Option Explicit at the top of your modules? You should. Read VB Editor Settings to find out why, and add it wherever it is missing.