Set Legend of a Chart from VBA?

Mike B in VT · 03-29-2006, 11:25 AM

I'm trying to set the Legend caption of a Chart by referencing a cell in a
different sheet but

ActiveChart.Legend.LegendEntries(1) =
ActiveWorkbook.Sheets("Sheet2").Range("F2").Value

gives me "Object doesn't Support this property or method".

Is there anyway to do this?

Jim Cone · 03-29-2006, 02:35 PM

Try...

ActiveChart.SeriesCollection(1).Name = ActiveWorkbook.Sheets("Sheet2").Range("F2").Text

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

"Mike B in VT" <MikeBinVT@discussions.microsoft.com>
wrote in message...
I'm trying to set the Legend caption of a Chart by referencing a cell in a
different sheet but

ActiveChart.Legend.LegendEntries(1) =
ActiveWorkbook.Sheets("Sheet2").Range("F2").Value

gives me "Object doesn't Support this property or method".
Is there anyway to do this?

Mike B in VT · 03-29-2006, 03:30 PM

Great that worked, Thanks Jim.

As a follow on:

I'm trying to dynamically determine the series info for the chart.
Basically an Access query is going to populate Sheet2, then when the User
opens the spreadsheet I've got code in WorkbookOpen() to refresh the chart.
But the # number of values to be charted will change each export. Rather
than hard coding in an arbitrary high range like (a2:a10000), I only want to
use cells that have data in them.

I've tried ActiveChart.SeriesCollection(1).Values = (Sheets(2).UsedRange)
but get a type mismatch.

"Jim Cone" wrote:

> Try...
>
> ActiveChart.SeriesCollection(1).Name = ActiveWorkbook.Sheets("Sheet2").Range("F2").Text
>
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
>
>
> "Mike B in VT" <MikeBinVT@discussions.microsoft.com>
> wrote in message...
> I'm trying to set the Legend caption of a Chart by referencing a cell in a
> different sheet but
>
> ActiveChart.Legend.LegendEntries(1) =
> ActiveWorkbook.Sheets("Sheet2").Range("F2").Value
>
> gives me "Object doesn't Support this property or method".
> Is there anyway to do this?
>

Jim Cone · 03-29-2006, 05:35 PM

Sounds like you could be exceeding the number of data points allowed.
Excel specifications (charts)...
Data series in one chart 255
Data points in a data series for 2-D charts 32,000
Data points in a data series for 3-D charts 4,000
Data points for all data series in one chart 256,000

The following code uses the data in column 1 for the series one values
and column 2 for series two and so on.
The "dots" are required.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

'------------------
Sub ChartSeriesTest()
Dim rngOne As Excel.Range
Dim rngTwo As Excel.Range
Dim rngThree As Excel.Range

With ActiveWorkbook.Sheets("Sheet2")
Set rngOne = .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
Set rngTwo = .Range("B1", .Cells(.Rows.Count, 2).End(xlUp))
Set rngThree = .Range("C1", .Cells(.Rows.Count, 3).End(xlUp))
End With

ActiveChart.SeriesCollection(1).Values = rngOne
ActiveChart.SeriesCollection(2).Values = rngTwo
ActiveChart.SeriesCollection(3).Values = rngThree

Set rngOne = Nothing
Set rngTwo = Nothing
Set rngThree = Nothing
End Sub
'---------------

"Mike B in VT" <MikeBinVT@discussions.microsoft.com>
wrote in message...
Great that worked, Thanks Jim.
As a follow on:
I'm trying to dynamically determine the series info for the chart.
Basically an Access query is going to populate Sheet2, then when the User
opens the spreadsheet I've got code in WorkbookOpen() to refresh the chart.
But the # number of values to be charted will change each export. Rather
than hard coding in an arbitrary high range like (a2:a10000), I only want to
use cells that have data in them.

I've tried ActiveChart.SeriesCollection(1).Values = (Sheets(2).UsedRange)
but get a type mismatch.

"Jim Cone" wrote:
> Try...
> ActiveChart.SeriesCollection(1).Name = ActiveWorkbook.Sheets("Sheet2").Range("F2").Text
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
>
>
> "Mike B in VT" <MikeBinVT@discussions.microsoft.com>
> wrote in message...
> I'm trying to set the Legend caption of a Chart by referencing a cell in a
> different sheet but
>
> ActiveChart.Legend.LegendEntries(1) =
> ActiveWorkbook.Sheets("Sheet2").Range("F2").Value
>
> gives me "Object doesn't Support this property or method".
> Is there anyway to do this?
>

Mike B in VT · 03-30-2006, 10:35 AM

Hi Jim,

When I try using that code, I get an error when trying to do the assign to
..Values:
Error 1004 "Application-defined or Object Defined Error"
Do I have to do it in a subfunction like you wrote?

Thanks for all the help,

Mike

"Jim Cone" wrote:

> Sounds like you could be exceeding the number of data points allowed.
> Excel specifications (charts)...
> Data series in one chart 255
> Data points in a data series for 2-D charts 32,000
> Data points in a data series for 3-D charts 4,000
> Data points for all data series in one chart 256,000
>
> The following code uses the data in column 1 for the series one values
> and column 2 for series two and so on.
> The "dots" are required.
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
> '------------------
> Sub ChartSeriesTest()
> Dim rngOne As Excel.Range
> Dim rngTwo As Excel.Range
> Dim rngThree As Excel.Range
>
> With ActiveWorkbook.Sheets("Sheet2")
> Set rngOne = .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
> Set rngTwo = .Range("B1", .Cells(.Rows.Count, 2).End(xlUp))
> Set rngThree = .Range("C1", .Cells(.Rows.Count, 3).End(xlUp))
> End With
>
> ActiveChart.SeriesCollection(1).Values = rngOne
> ActiveChart.SeriesCollection(2).Values = rngTwo
> ActiveChart.SeriesCollection(3).Values = rngThree
>
> Set rngOne = Nothing
> Set rngTwo = Nothing
> Set rngThree = Nothing
> End Sub
> '---------------
>
>
> "Mike B in VT" <MikeBinVT@discussions.microsoft.com>
> wrote in message...
> Great that worked, Thanks Jim.
> As a follow on:
> I'm trying to dynamically determine the series info for the chart.
> Basically an Access query is going to populate Sheet2, then when the User
> opens the spreadsheet I've got code in WorkbookOpen() to refresh the chart.
> But the # number of values to be charted will change each export. Rather
> than hard coding in an arbitrary high range like (a2:a10000), I only want to
> use cells that have data in them.
>
> I've tried ActiveChart.SeriesCollection(1).Values = (Sheets(2).UsedRange)
> but get a type mismatch.
>
>
>
> "Jim Cone" wrote:
> > Try...
> > ActiveChart.SeriesCollection(1).Name = ActiveWorkbook.Sheets("Sheet2").Range("F2").Text
> > Jim Cone
> > San Francisco, USA
> > http://www.realezsites.com/bus/primitivesoftware
> >
> >
> >
> > "Mike B in VT" <MikeBinVT@discussions.microsoft.com>
> > wrote in message...
> > I'm trying to set the Legend caption of a Chart by referencing a cell in a
> > different sheet but
> >
> > ActiveChart.Legend.LegendEntries(1) =
> > ActiveWorkbook.Sheets("Sheet2").Range("F2").Value
> >
> > gives me "Object doesn't Support this property or method".
> > Is there anyway to do this?
> >
>

Jim Cone · 03-30-2006, 11:35 AM

Mike,

Do you have the correct sheet name?
Are there dots in front of "Range", "Cells" and "Rows" ?
Is the chart active? (selected)

All code goes in a sub or a function, with exceptions for
module level declarations such as Option Explicit etc.
A separate sub or function is not required for my posted code.

Jim Cone

"Mike B in VT" <MikeBinVT@discussions.microsoft.com>
wrote in message...
Hi Jim,
When I try using that code, I get an error when trying to do the assign to
..Values:
Error 1004 "Application-defined or Object Defined Error"
Do I have to do it in a subfunction like you wrote?

Thanks for all the help,
Mike

"Jim Cone" wrote:
> Sounds like you could be exceeding the number of data points allowed.
> Excel specifications (charts)...
> Data series in one chart 255
> Data points in a data series for 2-D charts 32,000
> Data points in a data series for 3-D charts 4,000
> Data points for all data series in one chart 256,000
>
> The following code uses the data in column 1 for the series one values
> and column 2 for series two and so on.
> The "dots" are required.
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
> '------------------
> Sub ChartSeriesTest()
> Dim rngOne As Excel.Range
> Dim rngTwo As Excel.Range
> Dim rngThree As Excel.Range
>
> With ActiveWorkbook.Sheets("Sheet2")
> Set rngOne = .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
> Set rngTwo = .Range("B1", .Cells(.Rows.Count, 2).End(xlUp))
> Set rngThree = .Range("C1", .Cells(.Rows.Count, 3).End(xlUp))
> End With
>
> ActiveChart.SeriesCollection(1).Values = rngOne
> ActiveChart.SeriesCollection(2).Values = rngTwo
> ActiveChart.SeriesCollection(3).Values = rngThree
>
> Set rngOne = Nothing
> Set rngTwo = Nothing
> Set rngThree = Nothing
> End Sub
> '---------------

>
>
> "Mike B in VT" <MikeBinVT@discussions.microsoft.com>
> wrote in message...
> Great that worked, Thanks Jim.
> As a follow on:
> I'm trying to dynamically determine the series info for the chart.
> Basically an Access query is going to populate Sheet2, then when the User
> opens the spreadsheet I've got code in WorkbookOpen() to refresh the chart.
> But the # number of values to be charted will change each export. Rather
> than hard coding in an arbitrary high range like (a2:a10000), I only want to
> use cells that have data in them.
>
> I've tried ActiveChart.SeriesCollection(1).Values = (Sheets(2).UsedRange)
> but get a type mismatch.
>
>
>
> "Jim Cone" wrote:
> > Try...
> > ActiveChart.SeriesCollection(1).Name = ActiveWorkbook.Sheets("Sheet2").Range("F2").Text
> > Jim Cone
> > San Francisco, USA
> > http://www.realezsites.com/bus/primitivesoftware
> >
> >
> >
> > "Mike B in VT" <MikeBinVT@discussions.microsoft.com>
> > wrote in message...
> > I'm trying to set the Legend caption of a Chart by referencing a cell in a
> > different sheet but
> >
> > ActiveChart.Legend.LegendEntries(1) =
> > ActiveWorkbook.Sheets("Sheet2").Range("F2").Value
> >
> > gives me "Object doesn't Support this property or method".
> > Is there anyway to do this?
> >
>

Jim Cone · 03-30-2006, 11:45 AM

More...
Have you verified the number of data points in a column don't exceed
the specification limits?
Jim Cone

"Jim Cone" <jim.coneXXX@rcn.comXXX> wrote in message news:eBVkD9AVGHA.224@TK2MSFTNGP10.phx.gbl...
Mike,
Do you have the correct sheet name?
Are there dots in front of "Range", "Cells" and "Rows" ?
Is the chart active? (selected)
All code goes in a sub or a function, with exceptions for
module level declarations such as Option Explicit etc.
A separate sub or function is not required for my posted code.
Jim Cone

"Mike B in VT" <MikeBinVT@discussions.microsoft.com>
wrote in message...
Hi Jim,
When I try using that code, I get an error when trying to do the assign to
..Values:
Error 1004 "Application-defined or Object Defined Error"
Do I have to do it in a subfunction like you wrote?

Thanks for all the help,
Mike

"Jim Cone" wrote:
> Sounds like you could be exceeding the number of data points allowed.
> Excel specifications (charts)...
> Data series in one chart 255
> Data points in a data series for 2-D charts 32,000
> Data points in a data series for 3-D charts 4,000
> Data points for all data series in one chart 256,000
>
> The following code uses the data in column 1 for the series one values
> and column 2 for series two and so on.
> The "dots" are required.
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
> '------------------
> Sub ChartSeriesTest()
> Dim rngOne As Excel.Range
> Dim rngTwo As Excel.Range
> Dim rngThree As Excel.Range
>
> With ActiveWorkbook.Sheets("Sheet2")
> Set rngOne = .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
> Set rngTwo = .Range("B1", .Cells(.Rows.Count, 2).End(xlUp))
> Set rngThree = .Range("C1", .Cells(.Rows.Count, 3).End(xlUp))
> End With
>
> ActiveChart.SeriesCollection(1).Values = rngOne
> ActiveChart.SeriesCollection(2).Values = rngTwo
> ActiveChart.SeriesCollection(3).Values = rngThree
>
> Set rngOne = Nothing
> Set rngTwo = Nothing
> Set rngThree = Nothing
> End Sub
> '---------------

>
>
> "Mike B in VT" <MikeBinVT@discussions.microsoft.com>
> wrote in message...
> Great that worked, Thanks Jim.
> As a follow on:
> I'm trying to dynamically determine the series info for the chart.
> Basically an Access query is going to populate Sheet2, then when the User
> opens the spreadsheet I've got code in WorkbookOpen() to refresh the chart.
> But the # number of values to be charted will change each export. Rather
> than hard coding in an arbitrary high range like (a2:a10000), I only want to
> use cells that have data in them.
>
> I've tried ActiveChart.SeriesCollection(1).Values = (Sheets(2).UsedRange)
> but get a type mismatch.