Hello all,

I've run into a stupid issue, and I'm not sure how to get around it.

I'm trying to automatically change the data source of a chart. Basically
every month I need to move the data source ahead by a row (advance the chart
window by a month).

I'm trying to loop through the chart objects of the worksheet and I guess
there were numerous charts added then deleted. So my ChartObjects.Count
returns 13, but there are only 6 charts.

How can I either:
a) Reset the counter of ChartObjects?
b) Edit the chart names to be (for example) Chart 1 - Chart 6? Right now,
it's not consistent what charts were left. There's no continuous run of
values I can loop through.
c) Only look at valid charts? The chart has a name, so I can't screen out
using that variable. Maybe another is (consistently) missing from
non-viewable charts? I don't know.

Any ideas are appreciated.

Thanks,
Jay

--
Disregard, this is so I can find my post later.
***postedbyJay***

Avoid using the name:

Sub Tester1()
For Each ocht In ActiveSheet.ChartObjects
MsgBox ocht.Name & " - " & ocht.Chart.Name
Next

End Sub

but you can change the name manually. Make the drawing bar visible and
select the northwest arrow. Now click on the chart object and change the
name in the name bar. Reselect it and make sure it took. Turn on the macro
recorder while you do it and get the code to do it as well.

--
Regards,
Tom Ogilvy


--
regards,
Tom Ogilvy


"Jay" wrote:

Hello all,

I've run into a stupid issue, and I'm not sure how to get around it.

I'm trying to automatically change the data source of a chart. Basically
every month I need to move the data source ahead by a row (advance the chart
window by a month).

I'm trying to loop through the chart objects of the worksheet and I guess
there were numerous charts added then deleted. So my ChartObjects.Count
returns 13, but there are only 6 charts.

How can I either:
a) Reset the counter of ChartObjects?
b) Edit the chart names to be (for example) Chart 1 - Chart 6? Right now,
it's not consistent what charts were left. There's no continuous run of
values I can loop through.
c) Only look at valid charts? The chart has a name, so I can't screen out
using that variable. Maybe another is (consistently) missing from
non-viewable charts? I don't know.

Any ideas are appreciated.

Thanks,
Jay

--
Disregard, this is so I can find my post later.
***postedbyJay***

Jay,

If you are changing the data source of a chart, it would be easier to
use a named range - the chart always looks at the named range, but the
named range changes.

Is the data a time series and are the columns you are switching
between in the same workbook? Or have I misinterpreted your underlying
issue?

Toy.

Toy,

I hadn't thought of using a named range.

I've run into all sorts of issues trying to change the XValues and Values of
a SeriesCollection. I can set the value, but I can't retrieve the value.

I'm going to try the named range route. I can't find a way to retrieve the
XValues/Values for me to parse and increment.

Thanks!
--
Disregard, this is so I can find my post later.
***postedbyJay***


" wrote:

Jay,

If you are changing the data source of a chart, it would be easier to
use a named range - the chart always looks at the named range, but the
named range changes.

Is the data a time series and are the columns you are switching
between in the same workbook? Or have I misinterpreted your underlying
issue?

Toy.

One thing to note when using named ranges for chart series, ensure you
name your range for the worksheet only and not the workbook.

i.e. Sheet1!NamedRange

Then when refering to the name range you will need to put Sheet1!
NamedRange in the chart series.

Regards,

Toyin.

ps - I hope you can find your post later!

I found a small problem with this named range solution.

I have 30+ charts I need to create named ranges for. With 3+ series in each
chart.

I think I might be farther ahead if I can find a way to modify the XValues
and Values instead. That way it won't be dependant on the named ranges, and
then I won't have to create them all. ;)

Any ideas?
--
Disregard, this is so I can find my post later.
***postedbyJay***


" wrote:

One thing to note when using named ranges for chart series, ensure you
name your range for the worksheet only and not the workbook.

i.e. Sheet1!NamedRange

Then when refering to the name range you will need to put Sheet1!
NamedRange in the chart series.

Regards,

Toyin.

ps - I hope you can find your post later!

That solved my chart naming issues, thanks!
--
Disregard, this is so I can find my post later.
***postedbyJay***


"Tom Ogilvy" wrote:

Avoid using the name:

Sub Tester1()
For Each ocht In ActiveSheet.ChartObjects
MsgBox ocht.Name & " - " & ocht.Chart.Name
Next

End Sub

but you can change the name manually. Make the drawing bar visible and
select the northwest arrow. Now click on the chart object and change the
name in the name bar. Reselect it and make sure it took. Turn on the macro
recorder while you do it and get the code to do it as well.

--
Regards,
Tom Ogilvy


--
regards,
Tom Ogilvy


"Jay" wrote:

Hello all,

I've run into a stupid issue, and I'm not sure how to get around it.

I'm trying to automatically change the data source of a chart. Basically
every month I need to move the data source ahead by a row (advance the chart
window by a month).

I'm trying to loop through the chart objects of the worksheet and I guess
there were numerous charts added then deleted. So my ChartObjects.Count
returns 13, but there are only 6 charts.

How can I either:
a) Reset the counter of ChartObjects?
b) Edit the chart names to be (for example) Chart 1 - Chart 6? Right now,
it's not consistent what charts were left. There's no continuous run of
values I can loop through.
c) Only look at valid charts? The chart has a name, so I can't screen out
using that variable. Maybe another is (consistently) missing from
non-viewable charts? I don't know.

Any ideas are appreciated.

Thanks,
Jay

--
Disregard, this is so I can find my post later.
***postedbyJay***

Hi Jay,

Maybe someone assumed that deleting rows or columns containing charts also
deletes the charts, rather than making them zero width or height and
effectively invisible.

Sub FindNonDeletedCharts()
Dim chtobj As ChartObject
For Each chtobj In ActiveSheet.ChartObjects
If chtobj.Width < 20 Then
chtobj.Width = 200
chtobj.Chart.ChartArea.Interior.ColorIndex = 3
End If
If chtobj.Height < 20 Then
chtobj.Height = 100
chtobj.Chart.ChartArea.Interior.ColorIndex = 3
End If
Next

Regards,
Peter T

"Jay" wrote in message
...
Hello all,

I've run into a stupid issue, and I'm not sure how to get around it.

I'm trying to automatically change the data source of a chart. Basically
every month I need to move the data source ahead by a row (advance the
chart
window by a month).

I'm trying to loop through the chart objects of the worksheet and I guess
there were numerous charts added then deleted. So my ChartObjects.Count
returns 13, but there are only 6 charts.

How can I either:
a) Reset the counter of ChartObjects?
b) Edit the chart names to be (for example) Chart 1 - Chart 6? Right now,
it's not consistent what charts were left. There's no continuous run of
values I can loop through.
c) Only look at valid charts? The chart has a name, so I can't screen out
using that variable. Maybe another is (consistently) missing from
non-viewable charts? I don't know.

Any ideas are appreciated.

Thanks,
Jay

--
Disregard, this is so I can find my post later.
***postedbyJay***

That solved my 'ghost' chart issues, thanks!
--
Disregard, this is so I can find my post later.
***postedbyJay***


"Peter T" wrote:

Hi Jay,

Maybe someone assumed that deleting rows or columns containing charts also
deletes the charts, rather than making them zero width or height and
effectively invisible.

Sub FindNonDeletedCharts()
Dim chtobj As ChartObject
For Each chtobj In ActiveSheet.ChartObjects
If chtobj.Width < 20 Then
chtobj.Width = 200
chtobj.Chart.ChartArea.Interior.ColorIndex = 3
End If
If chtobj.Height < 20 Then
chtobj.Height = 100
chtobj.Chart.ChartArea.Interior.ColorIndex = 3
End If
Next

Regards,
Peter T

"Jay" wrote in message
...
Hello all,

I've run into a stupid issue, and I'm not sure how to get around it.

I'm trying to automatically change the data source of a chart. Basically
every month I need to move the data source ahead by a row (advance the
chart
window by a month).

I'm trying to loop through the chart objects of the worksheet and I guess
there were numerous charts added then deleted. So my ChartObjects.Count
returns 13, but there are only 6 charts.

How can I either:
a) Reset the counter of ChartObjects?
b) Edit the chart names to be (for example) Chart 1 - Chart 6? Right now,
it's not consistent what charts were left. There's no continuous run of
values I can loop through.
c) Only look at valid charts? The chart has a name, so I can't screen out
using that variable. Maybe another is (consistently) missing from
non-viewable charts? I don't know.

Any ideas are appreciated.

Thanks,
Jay

--
Disregard, this is so I can find my post later.
***postedbyJay***