• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 517
  • Last Modified:

Double click event for a chart in MS Excel sheet

I want to react on a double click event on a bar chart. So if a user clicks on a bar in a bar chart, I want to see it's value. It is a bar chart in a MS Excel sheet, not a chart page.
0
Frans_Truyens
Asked:
Frans_Truyens
  • 5
  • 3
  • 2
  • +1
1 Solution
 
James ElliottCommented:
I do not believe a chart has any events exposed in the object model, so even if you could get Excel to recognise that the chart has been double-clicked, you are very unlikely to be able to ascertain where the mouse is within the chart.

I wouldn't say however that this was impossible, only that it would take an ungodly amount of workarounds and API calls.
0
 
James ElliottCommented:
I'd recommend looking into some Business Intelligence software.
0
 
Frans_TruyensAuthor Commented:
I found on the internet some sites, www.databison, where they show it with a separate chart page in MS Excel. That works fine on a chart page, but I want to do it inside a normal worksheet. Any suggestions?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Frans_TruyensAuthor Commented:
I also found the following on the internet, but I can't get it to work. Can you help?

http://superuser.com/questions/851969/using-chart-beforedoubleclick-with-a-chart-object-in-worksheet
0
 
Rgonzo1971Commented:
HI,

pls try

in normal module

Global gclsDataPointValue As New cDataPointValue

Sub ActivateChart()
  If Not ActiveChart Is Nothing Then
    Set gclsDataPointValue.cht = ActiveChart
  Else
    MsgBox "Select a chart and try again.", vbExclamation, "No Active Chart"
  End If
End Sub

Sub DeactivateChart()
  Set gclsDataPointValue.cht = Nothing
End Sub

Open in new window

in a class module with name cDataPointValue
Public WithEvents cht As Chart


Private Sub cht_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
Cancel = True
If ElementID = 3 Then
    DataPts = ActiveChart.SeriesCollection(Arg1).Values
    MsgBox DataPts(Arg2)
End If

End Sub

Open in new window

Regards
cDataPointValue.xlsm
0
 
Frans_TruyensAuthor Commented:
Thanks Rgonzo1971. This solved my problem.
0
 
Frans_TruyensAuthor Commented:
Thanks this solved my problem.
0
 
Jon_PeltierCommented:
Excel charts expose all manner of events to the object model. I recently wrote a tutorial about this, Chart Events in Microsoft Excel.
0
 
Frans_TruyensAuthor Commented:
I will have a look. Thanks
0
 
James ElliottCommented:
Jon -> Nice tutorial. Do charts embedded within normal worksheets expose the same events?

Rgds
0
 
Jon_PeltierCommented:
James -

Yes, any code that works for a chart sheet will work in a class module for an embedded chart. It is important to properly reference the chart or the code will not work as expected.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now