Hi,
I have two charts in a chartsheet, an aerachart and a XY (Scatter) chart.
I have used Jon Peltier's AlignChart macro to align the charts. Then I use
Jon's Get X&Y on Any Charts macro to get data from the point I have clicked
on.
When I click on a X-Y-point, I get correct seriesnumber, pointnumber... but
when I click beside the a X-Y-point, I would like to get the seriesnumber and
pointnumber from the aerachart. In other words, I would like EXCEL to see
through the invisible part of the X-Y-chart and get data from the aerachart.
If I remove the X-Y-chart, I get desired information from the aerachart.
I can't figure out how to modify the Get X&Y on Any Charts macro so it
selects data from the aerachart when clicking beside a X-Y-point.

If I can get this problem fixed, then I would like to include this chart in
a PowerPoint presentaion and during Slideshow get data from the point I am
clicking on. Is this possible?

Sorry for my English but I hope you can understand my problem.

Thank you in advance for any help provided / boh

Not sure where you got Jon's code but here is what I believe is the "shell"
of the code from Jon's site:

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

Now, the issue is that "ActiveChart" is the one that got the click, which
will always be the top level chart (Z order). But, if you find the click did
not occur on one of your points from the activechart (see below) you could
switch to the chart beneath. I cannot vouch for this method since I have not
tested it but knowing you have set the chart sizes equal and your x and y
values should be the same for both charts I think it should work:

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

' NOTE I HAVE MOVED THE 'With ActiveChart' STATEMENT
' Pass x & y, return ElementID and Args
ActiveChart.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
With ActiveChart
' 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
'NOTE End With ALSO MOVES INSIDE IF STATEMENT
End With
End If
' ADD AN ELSE CLAUSE HERE FOR WHEN CLICK WAS NOT ON POINT(from top chart) SO
WE CAN CHECK THE NEXT CHART
Else
' DO SAME STEPS AS ABOVE, BUT NOW ON 2nd CHART:
Charts("2ndChartName").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
With Charts("2ndChartName")
' 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 With
End If

End If

End Sub

--
- K Dales


"boh" wrote:

Hi,
I have two charts in a chartsheet, an aerachart and a XY (Scatter) chart.
I have used Jon Peltier's AlignChart macro to align the charts. Then I use
Jon's Get X&Y on Any Charts macro to get data from the point I have clicked
on.
When I click on a X-Y-point, I get correct seriesnumber, pointnumber... but
when I click beside the a X-Y-point, I would like to get the seriesnumber and
pointnumber from the aerachart. In other words, I would like EXCEL to see
through the invisible part of the X-Y-chart and get data from the aerachart.
If I remove the X-Y-chart, I get desired information from the aerachart.
I can't figure out how to modify the Get X&Y on Any Charts macro so it
selects data from the aerachart when clicking beside a X-Y-point.

If I can get this problem fixed, then I would like to include this chart in
a PowerPoint presentaion and during Slideshow get data from the point I am
clicking on. Is this possible?

Sorry for my English but I hope you can understand my problem.

Thank you in advance for any help provided / boh