Solved

Double click event for a chart in MS Excel sheet

Posted on 2015-01-30
11
314 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
Office 365 Training for IT Pros

Learn how to provision Office 365 tenants, synchronize your on-premise Active Directory, and implement Single Sign-On.

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

738 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