Chart problem.

[boh]

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

[K Dales]

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
>
>

[boh]

Hi K Dales. Thanks for the answer. You have got the right "shell".

I had to change
With Charts("Chart 8") to
With ActiveSheet.ChartObjects("Chart 8").Charts to get rid of errorcodes.
I would lika to do something like Select Chart(Z-order - 1) because then I
dont have to know the name of the chart. Is this possible?

Now, when I click on the aerachart, I get data from the right chart and
right serienumber but Arg2 = -1 instead of pointnumber. If I remove the
X-Y-chart I get the pointnumber too. Any ideas? / boh

"K Dales" skrev:

> 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
> >
> >

[K Dales]

Well, pleased that it worked for you, at least in part, since I had not tried
it and was just speculating on how VBA would behave. I can't explain why you
are getting -1 for Arg2 because, if you follow the code, you are obtaining
Arg1 and Arg2 at the exact same time when you call .GetChartElement. So if
it gets the proper Arg1, why is it not getting Arg2? There must be a reason
but I can't figure it.

As far as selecting the chart by reference to the active chart: I think this
can be done but the only way I can think to do it is a bit cumbersome (and
again not tested). You would need to get the ActiveChart.Parent (to get the
ChartObject that contains it) and also the ActiveChart.Index to get its
location in the collection of charts for that chartobject. The location in
the collection corresponds to the Z-order, so if I am correct then
ActiveChart.Parent.Charts(ActiveChart.Index-1) will get the next chart
downward in the z-order (that is, the chart beneath the active chart).
--
- K Dales


"boh" wrote:

> Hi K Dales. Thanks for the answer. You have got the right "shell".
>
> I had to change
> With Charts("Chart 8") to
> With ActiveSheet.ChartObjects("Chart 8").Charts to get rid of errorcodes.
> I would lika to do something like Select Chart(Z-order - 1) because then I
> dont have to know the name of the chart. Is this possible?
>
> Now, when I click on the aerachart, I get data from the right chart and
> right serienumber but Arg2 = -1 instead of pointnumber. If I remove the
> X-Y-chart I get the pointnumber too. Any ideas? / boh
>
> "K Dales" skrev:
>
> > 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
> > >
> > >

[boh]

Hi, I got an error with
ActiveChart.Parent.Charts(ActiveChart.Index-1)
but when I tried
ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count - 1).Chart _
.GetChartElement X, Y, ElementID, Arg1, Arg2
I got data from the chart beneath the active chart. Excellent !

But when I use Active.Chart.ChartObjects..... I get an error.

Can that explain why Arg2 = -1, I mean EXCEL maybe can't get pointnumber
from a ActiveSheet....Chart ??

/ boh

"K Dales" skrev:

> Well, pleased that it worked for you, at least in part, since I had not tried
> it and was just speculating on how VBA would behave. I can't explain why you
> are getting -1 for Arg2 because, if you follow the code, you are obtaining
> Arg1 and Arg2 at the exact same time when you call .GetChartElement. So if
> it gets the proper Arg1, why is it not getting Arg2? There must be a reason
> but I can't figure it.
>
> As far as selecting the chart by reference to the active chart: I think this
> can be done but the only way I can think to do it is a bit cumbersome (and
> again not tested). You would need to get the ActiveChart.Parent (to get the
> ChartObject that contains it) and also the ActiveChart.Index to get its
> location in the collection of charts for that chartobject. The location in
> the collection corresponds to the Z-order, so if I am correct then
> ActiveChart.Parent.Charts(ActiveChart.Index-1) will get the next chart
> downward in the z-order (that is, the chart beneath the active chart).
> --
> - K Dales
>
>
> "boh" wrote:
>
> > Hi K Dales. Thanks for the answer. You have got the right "shell".
> >
> > I had to change
> > With Charts("Chart 8") to
> > With ActiveSheet.ChartObjects("Chart 8").Charts to get rid of errorcodes.
> > I would lika to do something like Select Chart(Z-order - 1) because then I
> > dont have to know the name of the chart. Is this possible?
> >
> > Now, when I click on the aerachart, I get data from the right chart and
> > right serienumber but Arg2 = -1 instead of pointnumber. If I remove the
> > X-Y-chart I get the pointnumber too. Any ideas? / boh