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!

 

[hrjohnson]

Hello,
So I moved the textbox declaration out of the mousemove event, yet the textboxes keep staying on the graph.

 

[Jon Peltier]

You want to keep the textboxes in the chart to have more control, but you want the declaration out of the procedure that creates and destroys the textbox, so that the variable keeps pointing to the textbox object when the procedure has ended and retriggered.

 

[hrjohnson]

So I should create one textbox, outside of the Mousemove out of the procedure, and pure control what is inside the textbox and the visibility of the textbox then?
Is there a method that controls visibility? And where should i put the textbox creation function?

 

[hrjohnson]

This is the code I added:

Private Sub EvtChart_Activate()
    Set txtbox = EvtChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 100, 100, 250, 180)
End Sub
 

Private Sub EvtChart_Deactivate()
         txtbox.Delete
End Sub

So I want to add a textbox when the chart is activated. And then delete it when I deactivate.

My next plans is to put txtbox.hide or txtbox.show or whatever when a series point has been click on, and then I would move the textbox, and change the data inside.

 

[Jon Peltier]

Hide the textbox when you create it.

Private Sub EvtChart_Activate()
    Set txtbox = EvtChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 100, 100, 250, 180)
    txtbox.Visible = False
End Sub

Then later instead of creating and destroying it in the mouse move event procedure, move the textbox, change its text, and make it visible (or invisible, based on what element is under the mouse arrow).

 

[hrjohnson]

This solution works great! Thank you for all your help!