Solved

Double click event for a chart in MS Excel sheet

Posted on 2015-01-30
11
340 Views
Last Modified: 2016-02-11
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
Comment
Question by:Frans_Truyens
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 12

Expert Comment

by:James Elliott
ID: 40579441
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
 
LVL 12

Expert Comment

by:James Elliott
ID: 40579443
I'd recommend looking into some Business Intelligence software.
0
 

Author Comment

by:Frans_Truyens
ID: 40579472
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Frans_Truyens
ID: 40579549
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
 
LVL 51

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40579564
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
 

Author Comment

by:Frans_Truyens
ID: 40579635
Thanks Rgonzo1971. This solved my problem.
0
 

Author Closing Comment

by:Frans_Truyens
ID: 40579636
Thanks this solved my problem.
0
 
LVL 1

Expert Comment

by:Jon_Peltier
ID: 40584391
Excel charts expose all manner of events to the object model. I recently wrote a tutorial about this, Chart Events in Microsoft Excel.
0
 

Author Comment

by:Frans_Truyens
ID: 40584473
I will have a look. Thanks
0
 
LVL 12

Expert Comment

by:James Elliott
ID: 40585809
Jon -> Nice tutorial. Do charts embedded within normal worksheets expose the same events?

Rgds
0
 
LVL 1

Expert Comment

by:Jon_Peltier
ID: 40586746
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question