• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Charting events

Hi All


I have been looking into charting events to come up with a solution for my problem.


I have an interactive dashboard, Chandoo style, and I want it so that when I click on a bar in a bar chart, a pie chart pops up with subsequent information about that bar. So if that bar was a total of yearly sales, I want to click on it, producing a pie chart that breaks that bar down into quarterly sales values.


I posted a question on these forums about a month ago requesting how to do this, but have only just really needed to find a solution recently. NARAYANK kindly gave me this link: http://www.computorcompanion.com/LPMArticle.asp?ID=221 which I have been working on today and have stumbled across a block in the road, which hopefully somebody will be able to help me with!


So here is some code:

[pre]
Code:
Private Sub Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long, _
ByVal x As Long, ByVal y As Long)

Dim ElementID As Long, Arg1 As Long, Arg2 As Long
Dim myX As Variant, myY As Double

With ActiveChart
' Pass x & y, return ElementID and Args
.GetChartElement x, y, ElementID, Arg1, Arg2

' Did we click over a point or data label?
If ElementID = xlSeries Or ElementID = xlDataLabel Then
If Arg2 > 0 Then
' Extract x value from array of x values
myX = WorksheetFunction.Index _
(.SeriesCollection(Arg1).XValues, Arg2)
' Extract y value from array of y values
myY = WorksheetFunction.Index _
(.SeriesCollection(Arg1).Values, Arg2)

' Display message box with point information
MsgBox "Series " & Arg1 & vbCrLf _
& """" & .SeriesCollection(Arg1).Name & """" & vbCrLf _
& "Point " & Arg2 & vbCrLf _
& "X = " & myX & vbCrLf _
& "Y = " & myY
End If
End If
End With

End Sub
[/pre]

What this does is when you click on a bar, it will pop up with a messagebox with information about that bar.


I want to capture this info somehow and use it to build the pie chart, and this is where I'm stuck. The guide continues with:


"You could do many things with the data from this procedure. A message box requires a user response to continue. Replace this by drawing a textbox with the same information, as a smarter chart tip. Use the MouseMove event instead, to mimic Excel's normal chart tip behavior.


Place this data into a worksheet range. After clicking on several points, you would have a small table of values from the selected points. These could be used for subsequent analysis."


This is exactly what I need, but I don't understand how to grab this information to put into a worksheet range, but if somebody could briefly explain how I would do this, then I could have a pie chart linked to the range which will change when I click on different bars, thus producing the solution im after!


Could anybody please elaborate or help me out? I'm very new to VBA and am trying my best to learn as much as possible.


Thanks!

EJ
 

jeffreyweir

Active Member
Hi EJ. I don't quite folllow. You say:

What this does is when you click on a bar, it will pop up with a messagebox with information about that bar.

I want to capture this info somehow and use it to build the pie chart."


Is the bar chart a stacked bar chart with multiple numbers per bar?


Perhaps best you upload a sample workbook, showing what you data looks like, and showing what you want to achieve. (including some sample pie charts that relate to the various bars).


As the old saying goes, a sample workbook is worth a thousand words"
 
Hi Jeffreyweir


Sorry that I didn't link a workbook; https://www.dropbox.com/s/2bquunsyjauxdx4/book2.xlsm


If you go onto chart1 you will see the graph (which takes info from table1), and if you click on a bar you will see what happens with the message box.


What I want ideally, if you look at table 2, is when I click on a bar, I want a pie chart to form showing a breakdown of that bar (breakdown is q1-q4 in table 2 on sheet1), so to show q1-q4 in a pie chart which totals to the "alpha total". Then If I click on another bar, it will show q1-q4 for that bar the same pie chart (so the pie chart will change to capture the data in the second bar), etc.


I know this is possible, but I just don't know how. I think it is somewhere along the lines of the data from the first bar will be able to be captured and put into a spread sheet range, and then have a pie chart taking data from this range.


I have made some workarounds to this problem, https://www.dropbox.com/s/gvt1jgg0pw783td/Book1.xlsm , but the workarounds are really just cheats in excel which will not really work with a dynamic graph. If you look at this workbook you will see what I want to achieve, so if you click on an actual bar in the column charts in book 1, you will see what happens to the pie chart. The reason this wont work with a dynamic graph, is because the way it currently works is I have set up invisible boxes behind the current bars, so that when you click on a bar you are actually clicking on an invisible box which then runs a macro and changes the pie chart accordingly. If however, the column chart needs to change number of columns dynamically, then the invisible boxes will be out of line and not work correctly; which is the reason why I want to find a VBA solution to my problem.


My manager requested this, saying "I want it so that if I click on a bar on the bar chart (or column chart), a pie chart pops out somewhere on the page showing a breakdown of that bar".


Thanks in advance for your help; any pointers will be great, even if you link me a page that may help me.


EJ
 

jeffreyweir

Active Member
I take it you know VBA? There's heaps on the net regarding chart events. Give 'Chart Events' a google, and see what floats to the top.
 

NARAYANK991

Excel Ninja
Hi ,


I think you are trying to complicate the matter ; the meaning of chart events , which is what the link you already have explains , is that when you perform an action with the mouse , that event can be recognized by VBA code , and appropriate actions can be taken by the code. The use of classes ensures that one piece of code can serve for many objects ; if you did not use classes , you would need to repeat at least some lines of code for every object which needs to have programmability associated with it.


These chart event procedures will be analogous to the Worksheet_SelectionChange events which allow you to take appropriate action when ever the active worksheet cell is changed , either through the keyboard cursor movement keys or by using the mouse.


A 100 % dynamic chart which builds up the output chart from scratch based on where on the displayed chart the user has clicked , will be quite a lengthy and complex piece of code.


A simpler workaround is one where you build all the required charts manually , beforehand , and the chart event code just uses the mouse clicks to insert the desired chart in the drawing space ; if you click on bar 1 , chart 101 will be displayed , if you click on bar 2 , chart 113 may be displayed , if you click on bar 3 , chart 131 may be displayed and so on. The association between the bar which has been clicked on , and the chart which is to be displayed , may be a straightforward one using a numbering scheme , or it can be taken from a lookup table which has been inserted in any worksheet.


Narayan
 
Hi Narayan


Thanks for your input, I realise its a very complex task (one which is way out of my depth), but its what the manager wants so I have to at least try my best. My solution that I created works fine, but only for static charts, not for dynamic ones; Ideally I need a solution for a dynamic chart. I will have a look into pivotcharts as I heard that you can get a "drilldown" much easier through pivotcharts.
 
Top