Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Double click event for a chart in MS Excel sheet

Posted on 2015-01-30
11
Medium Priority
?
471 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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 53

Accepted Solution

by:
Rgonzo1971 earned 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This video walks the viewer through the process of creating an MLA formatted document, as well as a bibliography with citations.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

610 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