MRichmond
08-15-2011, 11:22 PM
Good day to all you VBA gurus.
I have a chart that contains the answers to a range of questions (divided into 5 categories), and due to the length of the questions the X - axis only contains the label "Question 1", "Question 2" etc. up to "Question 34".
Courtesy of Jon Peltier & Ian Coulson I have managed to cobble together the VBA code below, that allows a user to see the question in full (in a mesage box) when they click on any of the data points on the chart.
This code works brilliantly when all questions are shown, however if the user selects to view only questions belonging to one category (user clicks a button which hides the all rows not required on data sheet, thus displaying on graph on relevant qustions), the message box no longer displays the right question. For example if the user chooses to see questions relating to transport, which are questions 12 - 16, the user sees the results for the qestion correctly, but the message box displays the full questions for questions 1 - 5).
Does anyone know how I can adapt the code so it will show the correct questions when the filtering is done?
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 "Point " & Arg2 & vbCrLf _
& " - " & Worksheets("Data").Range("H17:H50").Cells(Arg2, 1).Value
End If
End If
End With
End Sub
Thanks in advance for any & all assistance given.
Edited to add sample file
I have a chart that contains the answers to a range of questions (divided into 5 categories), and due to the length of the questions the X - axis only contains the label "Question 1", "Question 2" etc. up to "Question 34".
Courtesy of Jon Peltier & Ian Coulson I have managed to cobble together the VBA code below, that allows a user to see the question in full (in a mesage box) when they click on any of the data points on the chart.
This code works brilliantly when all questions are shown, however if the user selects to view only questions belonging to one category (user clicks a button which hides the all rows not required on data sheet, thus displaying on graph on relevant qustions), the message box no longer displays the right question. For example if the user chooses to see questions relating to transport, which are questions 12 - 16, the user sees the results for the qestion correctly, but the message box displays the full questions for questions 1 - 5).
Does anyone know how I can adapt the code so it will show the correct questions when the filtering is done?
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 "Point " & Arg2 & vbCrLf _
& " - " & Worksheets("Data").Range("H17:H50").Cells(Arg2, 1).Value
End If
End If
End With
End Sub
Thanks in advance for any & all assistance given.
Edited to add sample file