Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart point value
Hi all
How can I address a specific data point on a chart to give back the specific (y)value? the following does not work: MsgBox (ActiveChart.SeriesCollection(1).Points(1).Value) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart point value
Claude,
Does the code below do what you want? The referencing of the chart depends upon whether it is a standalone chart sheet or an embedded chart on a worksheet. Troy Sub WorksheetEmbeddedChart() Dim ws As Worksheet Dim cht As Chart Dim ser1 As Series Dim ii As Integer Set ws = ThisWorkbook.Worksheets("Sheet1") 'Note: the name of the chart is: "Chart(space)1". Set cht = ws.ChartObjects("Chart 1").Chart Set ser1 = cht.SeriesCollection(1) ii = 2 MsgBox "Series1(" & ii & "): x=" & _ ser1.XValues(ii) & ", y=" & ser1.Values(ii) End Sub Sub ChartSheet() Dim cht As Chart Dim ser1 As Series Dim ii As Integer 'Note: the name of the chart is: "Chart1". Set cht = ThisWorkbook.Charts("Chart1") Set ser1 = cht.SeriesCollection(1) ii = 2 MsgBox "Series1(" & ii & "): x=" & _ ser1.XValues(ii) & ", y=" & ser1.Values(ii) End Sub "Claude" wrote in message ... Hi all How can I address a specific data point on a chart to give back the specific (y)value? the following does not work: MsgBox (ActiveChart.SeriesCollection(1).Points(1).Value) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart point value
I think it's easiest if you first assign all the values to an array,
then use the array to access the individual values. eg. Dim i As Integer Dim v As Variant v = ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1).Values For i = 1 To UBound(v) MsgBox v(i) Next i Regards, Vic Eldridge "Claude" wrote in message ... Hi all How can I address a specific data point on a chart to give back the specific (y)value? the following does not work: MsgBox (ActiveChart.SeriesCollection(1).Points(1).Value) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart point value
Well now I'm confused.
Check out this little macro. The first MsgBox works but the second one fails. She's a fussy old thing hey ? Sub Huh() Dim srs As Series Set srs = ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1) MsgBox srs.Values(1) MsgBox ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1).Values(1) End Sub Regards, Vic Eldridge "TroyW" wrote in message ... Claude, Does the code below do what you want? The referencing of the chart depends upon whether it is a standalone chart sheet or an embedded chart on a worksheet. Troy Sub WorksheetEmbeddedChart() Dim ws As Worksheet Dim cht As Chart Dim ser1 As Series Dim ii As Integer Set ws = ThisWorkbook.Worksheets("Sheet1") 'Note: the name of the chart is: "Chart(space)1". Set cht = ws.ChartObjects("Chart 1").Chart Set ser1 = cht.SeriesCollection(1) ii = 2 MsgBox "Series1(" & ii & "): x=" & _ ser1.XValues(ii) & ", y=" & ser1.Values(ii) End Sub Sub ChartSheet() Dim cht As Chart Dim ser1 As Series Dim ii As Integer 'Note: the name of the chart is: "Chart1". Set cht = ThisWorkbook.Charts("Chart1") Set ser1 = cht.SeriesCollection(1) ii = 2 MsgBox "Series1(" & ii & "): x=" & _ ser1.XValues(ii) & ", y=" & ser1.Values(ii) End Sub "Claude" wrote in message ... Hi all How can I address a specific data point on a chart to give back the specific (y)value? the following does not work: MsgBox (ActiveChart.SeriesCollection(1).Points(1).Value) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart point value
Hi Vic
Sorry for the late reply. The idea with first adding the values to an array is a simple solution that works, thanks! Thanks also to Troy for pointing out the XValues(i) syntax (also I was struggling to implement it: maybe it's because I'm still using excel97...) -----Original Message----- Well now I'm confused. Check out this little macro. The first MsgBox works but the second one fails. She's a fussy old thing hey ? Sub Huh() Dim srs As Series Set srs = ActiveSheet.ChartObjects (1).Chart.SeriesCollection(1) MsgBox srs.Values(1) MsgBox ActiveSheet.ChartObjects (1).Chart.SeriesCollection(1).Values(1) End Sub Regards, Vic Eldridge "TroyW" wrote in message ... Claude, Does the code below do what you want? The referencing of the chart depends upon whether it is a standalone chart sheet or an embedded chart on a worksheet. Troy Sub WorksheetEmbeddedChart() Dim ws As Worksheet Dim cht As Chart Dim ser1 As Series Dim ii As Integer Set ws = ThisWorkbook.Worksheets("Sheet1") 'Note: the name of the chart is: "Chart(space)1". Set cht = ws.ChartObjects("Chart 1").Chart Set ser1 = cht.SeriesCollection(1) ii = 2 MsgBox "Series1(" & ii & "): x=" & _ ser1.XValues(ii) & ", y=" & ser1.Values(ii) End Sub Sub ChartSheet() Dim cht As Chart Dim ser1 As Series Dim ii As Integer 'Note: the name of the chart is: "Chart1". Set cht = ThisWorkbook.Charts("Chart1") Set ser1 = cht.SeriesCollection(1) ii = 2 MsgBox "Series1(" & ii & "): x=" & _ ser1.XValues(ii) & ", y=" & ser1.Values(ii) End Sub "Claude" wrote in message ... Hi all How can I address a specific data point on a chart to give back the specific (y)value? the following does not work: MsgBox (ActiveChart.SeriesCollection(1).Points (1).Value) . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart point value
But this line works in place of your second:
MsgBox WorksheetFunction.Index(ActiveSheet.ChartObjects(1 ) _ .Chart.SeriesCollection(1).Values, 1) Go figure. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Vic Eldridge wrote: Well now I'm confused. Check out this little macro. The first MsgBox works but the second one fails. She's a fussy old thing hey ? Sub Huh() Dim srs As Series Set srs = ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1) MsgBox srs.Values(1) MsgBox ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1).Values(1) End Sub Regards, Vic Eldridge "TroyW" wrote in message ... Claude, Does the code below do what you want? The referencing of the chart depends upon whether it is a standalone chart sheet or an embedded chart on a worksheet. Troy Sub WorksheetEmbeddedChart() Dim ws As Worksheet Dim cht As Chart Dim ser1 As Series Dim ii As Integer Set ws = ThisWorkbook.Worksheets("Sheet1") 'Note: the name of the chart is: "Chart(space)1". Set cht = ws.ChartObjects("Chart 1").Chart Set ser1 = cht.SeriesCollection(1) ii = 2 MsgBox "Series1(" & ii & "): x=" & _ ser1.XValues(ii) & ", y=" & ser1.Values(ii) End Sub Sub ChartSheet() Dim cht As Chart Dim ser1 As Series Dim ii As Integer 'Note: the name of the chart is: "Chart1". Set cht = ThisWorkbook.Charts("Chart1") Set ser1 = cht.SeriesCollection(1) ii = 2 MsgBox "Series1(" & ii & "): x=" & _ ser1.XValues(ii) & ", y=" & ser1.Values(ii) End Sub "Claude" wrote in message .. . Hi all How can I address a specific data point on a chart to give back the specific (y)value? the following does not work: MsgBox (ActiveChart.SeriesCollection(1).Points(1).Value) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I link comments on the chart to a point on the chart? | Charts and Charting in Excel | |||
Series point on a chart | Excel Discussion (Misc queries) | |||
Return value of a point in a bar chart | Charts and Charting in Excel | |||
Chart Point Analysis | Charts and Charting in Excel | |||
finding the value of a point on chart | Charts and Charting in Excel |