Solved: Chart Labels need to be dynamic?

[MRichmond]

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?

[vba]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
[/vba]

Thanks in advance for any & all assistance given.

Edited to add sample file

[Bob Phillips]

[vba]

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.Match _
(myX, Worksheets("Data").Columns("A"), 0)

' Display message box with point information
MsgBox "Point " & Arg2 & vbCrLf _
& " - " & Worksheets("Data").Cells(myY, "H").Value

End If
End If
End With
End Sub[/vba]

[MRichmond]

Thanks for the response XLD, however when I run it I get an error
"Run-Time Error 13", Type Mismatch, and when i press debug it highlights these two rows of code

myY = WorksheetFunction.Match _
(myX, Worksheets("Data").Columns("A"), 0)

Any ideas?

[Aflatoon]

It appears to me that you need to change this line:
[vba]
MsgBox "Point " & Arg2 & vbCrLf _
& " - " & Worksheets("Data").Range("H17:H50").Cells(Arg2, 1).Value [/vba]

to this:
[vba]
MsgBox "Point " & Arg2 & vbCrLf _
& " - " & myX [/vba]

[MRichmond]

Thanks Aflatoon,

almost, but not quite.

It does now change to reflect the questions displayed, but it doesnt show the actual question any longer, it justs Question 8 (from column A?), not the actual question text (from column H)

[Bob Phillips]

Sorry about that, I tested in XL2007 and it works fine there. In XL2003 it bombs.

Try this

[vba]

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
Dim tmp As String

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
Worksheets("Data").Activate
myY = WorksheetFunction.Match _
(myX, Worksheets("Data").Columns("A"), 0)

' Display message box with point information
MsgBox "Point " & Arg2 & vbCrLf _
& " - " & Worksheets("Data").Cells(myY, "H").Value

End If
End If
End With
End Sub
[/vba]

[Aflatoon]

My apologies - I did not look at the data closely enough. You merely need to alter xld's code to use:
[vba] myY = WorksheetFunction.Match _
(myX, Worksheets("Data").Range("A:A"), 0)
[/vba]

[MRichmond]

Thanks very much guys, that works great .

As always the help you all give is brilliant, and is so gratefully recieved.