exceljockey
Member
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]
[/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
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
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