Solved

Double click event for a chart in MS Excel sheet

Posted on 2015-01-30
11
198 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
  • 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
 

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 48

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now