Getting Chart "Cursor" information in Excel 2003

[INTP56]

I thought I posted something along these lines yesterday, but ....

What I would like is for the user to hold the mouse over a point in a series
on a chart, and somehow signal me (Event) so I can find the corresponding row
in the series. If I could get the information in the tooltip text, I could
look for that.

I generate charts on the fly, so I can't put code under the charts
themselves. Any ideas on how to do that, or if it can be done?

Thanks, Bob

P.S. I am familiar making Class Modules containing a WithEvents Application
object, but at this point, I can't see how to accomplish what I'm looking for
with that methodology. i.e. I don't see chart events.

 

[Jon Peltier]

This should help get you started:

http://www.computorcompanion.com/LPMArticle.asp?ID=221

- Jon

 

[INTP56]

Jon,

Thank you very much. I was able to write something that works for any
chart, even ones the user created. The only limitation seems to be it has to
be in it's own sheet, it can't be embedded. For those interested, this is
what I did:

Bob

In ThisWorkbook object, I had the following code:
'************************************
Option Explicit
Private Sub Workbook_Open()
Workbook_AddinInstall
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Workbook_AddinUninstall
End Sub
Private Sub Workbook_AddinInstall()
LoadWB
End Sub
Private Sub Workbook_AddinUninstall()
UnloadWB
End Sub
'*******************************

I created a class module named clsAppEvent, and had the following code
'*****************************************************
Option Explicit
Private WithEvents mappExcel As Application
Private Sub Class_Initialize()
Set mappExcel = Application
End Sub
Private Sub Class_Terminate()
Set mappExcel = Nothing
End Sub
Private Sub mappExcel_SheetActivate(ByVal Sh As Object)
Set MyCodeModule.chtActive = ActiveChart
End Sub
Private Sub mappExcel_SheetDeactivate(ByVal Sh As Object)
Set MyCodeModule.chtActive = Nothing
End Sub
'********************************************
I created another Class Module, named clsChartEvent, and put this in.
'*************************************************
Option Explicit
Public WithEvents chtActive As Chart
Private Sub chtActive_MouseUp(ByVal Button As Long, ByVal Shift As Long,
ByVal x As Long, ByVal y As Long)
Dim lngElementID As Long, lngArg1 As Long, lngArg2 As Long
If Shift = 4 Then
If Button = xlPrimaryButton Then
With chtActive
.GetChartElement x, y, lngElementID, lngArg1, lngArg2
If lngElementID = xlSeries Or lngElementID = xlDataLabel Then
MsgBox .SeriesCollection(lngArg1).Name & " Point " &
CStr(lngArg2)
End If
End With
End If
End If
End Sub
'*********************************************************
Finally, I created a Regular module with the following code:
'*****************************************************
Option Explicit
Private gchtActive As clsChartEvent
Private gappExcel As clsAppEvent
Private mblnIsModuleLoaded As Boolean
Property Set chtActive(chtCurrent As Chart)
Set gchtActive.chtActive = chtCurrent
End Property
Property Get chtActive() As Chart
Set chtActive = gchtActive.chtActive
End Property
Public Sub LoadWB()
Set gappExcel = New clsAppEvent
Set gchtActive = New clsChartEvent
mblnIsModuleLoaded = True
End Sub
Public Sub UnloadWB()
Set gchtActive = Nothing
Set gappExcel = Nothing
mblnIsModuleLoaded = False
End Sub
'**************************************************

 

[Jon Peltier]

This procedure

Private Sub mappExcel_SheetActivate(ByVal Sh As Object)
Set MyCodeModule.chtActive = ActiveChart
End Sub

only activates a chart if the chart is the active sheet or if the active
sheet contains a chart which is selected. Doesn't that article show how to
activate any and all charts on a sheet? When the sheet is activated, all
charts become event-enabled (using an array of charts, not just a single
chart), so when you start clicking, the code will work for the chart being
clicked on.

- Jon

 

[INTP56]

Jon,

I didn't mean to imply what you had in your article had that limitation, it
was my limitation. After rereading my response I felt I should make that
clear.

In my case, all my charts will be on their own tab. The real problem I was
trying to solve was to have my user look at a chart, click a point on a
series, show the data label ( hide the previous one if necessary) and write
an average formula in a cell in a worksheet.

The version I'm using works great, and I thank you for steering me towards a
very clean solution and another trick for my toolbag.

Bob