looping through series

[raymondvillain]

I need to do the same thing to every series on a chart. Some of my charts
have 8 to 10 series lines drawn on them. I know how to write a loop, but I
don't know what the maximum number of SeriesCollection(?)'s is. I want to do
something like

While [what should the test be?]
ActiveChart.SeriesCollection(?).Border.Weight = 1
Wend

Does this make sense?

 

[OssieMac]

Hi,

Will something like this achieve your desired outcome. You don't need to
test when it is finished because .SeriesCollection.Count sets the number of
times to loop.

Set you border weight to whatever you want.

Sub Test()
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart
For i = 1 To .SeriesCollection.Count
.SeriesCollection(i).Border.Weight = xlThick
Next i
End With
End Sub

Regards,

OssieMac

 

[raymondvillain]

Thanks so much, OssieMac! One more thing. If I want to set the color black,
would I insert a line into your code like this?
.SeriesColeection(i).Border.ColorIndex = 0

I am totally ignorant about the color naming schemes. I went to the
pallette and looked around to get black and it seemed like RGB values were 0,
0, and 0. When I tried the line above, it changed all colors to something
besides black and also changed the line thicknesses. Do you have any
suggestions?
Thanks in Advance,
Raymondvillain
--
leave well enough alone

Hi,

Will something like this achieve your desired outcome. You don't need to
test when it is finished because .SeriesCollection.Count sets the number of
times to loop.

Set you border weight to whatever you want.

Sub Test()
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart
For i = 1 To .SeriesCollection.Count
.SeriesCollection(i).Border.Weight = xlThick
Next i
End With
End Sub

Regards,

OssieMac

I need to do the same thing to every series on a chart. Some of my charts
have 8 to 10 series lines drawn on them. I know how to write a loop, but I
don't know what the maximum number of SeriesCollection(?)'s is. I want to do
something like

While [what should the test be?]
ActiveChart.SeriesCollection(?).Border.Weight = 1
Wend

Does this make sense?

 

[OssieMac]

Hi again,

Black is ColorIndex 1. You have a typo in SeriesCollection but otherwise you
are correct in the method.

Do you know how to record a macro? It is the greatest method of finding out
the syntax and it would have given you the above. You often get a lot more
info than you want from a recorded macro but you can extract the essential
part for your program.

If you don't know how to record then let me know what version of xl you are
using (ie xl2007 or earlier version) and I'll tell you how to get started.

Regards,

OssieMac

Thanks so much, OssieMac! One more thing. If I want to set the color black,
would I insert a line into your code like this?
.SeriesColeection(i).Border.ColorIndex = 0

I am totally ignorant about the color naming schemes. I went to the
pallette and looked around to get black and it seemed like RGB values were 0,
0, and 0. When I tried the line above, it changed all colors to something
besides black and also changed the line thicknesses. Do you have any
suggestions?
Thanks in Advance,
Raymondvillain
--
leave well enough alone

Hi,

Will something like this achieve your desired outcome. You don't need to
test when it is finished because .SeriesCollection.Count sets the number of
times to loop.

Set you border weight to whatever you want.

Sub Test()
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart
For i = 1 To .SeriesCollection.Count
.SeriesCollection(i).Border.Weight = xlThick
Next i
End With
End Sub

Regards,

OssieMac

I need to do the same thing to every series on a chart. Some of my charts
have 8 to 10 series lines drawn on them. I know how to write a loop, but I
don't know what the maximum number of SeriesCollection(?)'s is. I want to do
something like

While [what should the test be?]
ActiveChart.SeriesCollection(?).Border.Weight = 1
Wend

Does this make sense?

 

[raymondvillain]

Thanks so much for your help. Should have stated that am using Excel 2007.

I have recorded many macros over time, but in Excel 2007, there appears to
be a problem. I activate a chart, click on "record macro", then select a
series line, change linestyle, color, etc., click on "stop recording". Then
when I open the macro in VBA, nothing is there but:

Sub new_demo()
'
' new_demo Macro
' Keyboard Shortcut: Ctrl+h
'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveSheet.ChartObjects("Chart 1").Activate
End Sub

What do you suggest? I could go back to Excel 97 on an older machine,
select chart, record macro, etc. and then open the workbook in Excel 2007 and
go from there.

Are there any reference books that you know of that list these features,
such as the black color being 1, etc.? I agree that recording a macro is a
great way to figure out the intricacies of Visual Basic.

Thanks again for your time,
Raymondvillain
--
leave well enough alone

Hi again,

Black is ColorIndex 1. You have a typo in SeriesCollection but otherwise you
are correct in the method.

Do you know how to record a macro? It is the greatest method of finding out
the syntax and it would have given you the above. You often get a lot more
info than you want from a recorded macro but you can extract the essential
part for your program.

If you don't know how to record then let me know what version of xl you are
using (ie xl2007 or earlier version) and I'll tell you how to get started.

Regards,

OssieMac

Thanks so much, OssieMac! One more thing. If I want to set the color black,
would I insert a line into your code like this?
.SeriesColeection(i).Border.ColorIndex = 0

I am totally ignorant about the color naming schemes. I went to the
pallette and looked around to get black and it seemed like RGB values were 0,
0, and 0. When I tried the line above, it changed all colors to something
besides black and also changed the line thicknesses. Do you have any
suggestions?
Thanks in Advance,
Raymondvillain
--
leave well enough alone

Hi,

Will something like this achieve your desired outcome. You don't need to
test when it is finished because .SeriesCollection.Count sets the number of
times to loop.

Set you border weight to whatever you want.

Sub Test()
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart
For i = 1 To .SeriesCollection.Count
.SeriesCollection(i).Border.Weight = xlThick
Next i
End With
End Sub

Regards,

OssieMac


:

I need to do the same thing to every series on a chart. Some of my charts
have 8 to 10 series lines drawn on them. I know how to write a loop, but I
don't know what the maximum number of SeriesCollection(?)'s is. I want to do
something like

While [what should the test be?]
ActiveChart.SeriesCollection(?).Border.Weight = 1
Wend

Does this make sense?

 

[OssieMac]

I tested the macro recording on xl2007 and I see what you mean. I have xl2002
on one machine and 2007 on my new one. I have not moved everything on to the
new one yet and still often use 2002. Macro recording with the charts works
fine on it.

Answer to your other question, in xl2007, open the VBA editor and open Help
from there and search for color index . Select ColorIndex Property. Similar
search in xl2002 VBA editor.

Regards,

OssieMac

Thanks so much for your help. Should have stated that am using Excel 2007.

I have recorded many macros over time, but in Excel 2007, there appears to
be a problem. I activate a chart, click on "record macro", then select a
series line, change linestyle, color, etc., click on "stop recording". Then
when I open the macro in VBA, nothing is there but:

Sub new_demo()
'
' new_demo Macro
' Keyboard Shortcut: Ctrl+h
'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveSheet.ChartObjects("Chart 1").Activate
End Sub

What do you suggest? I could go back to Excel 97 on an older machine,
select chart, record macro, etc. and then open the workbook in Excel 2007 and
go from there.

Are there any reference books that you know of that list these features,
such as the black color being 1, etc.? I agree that recording a macro is a
great way to figure out the intricacies of Visual Basic.

Thanks again for your time,
Raymondvillain
--
leave well enough alone

Hi again,

Black is ColorIndex 1. You have a typo in SeriesCollection but otherwise you
are correct in the method.

Do you know how to record a macro? It is the greatest method of finding out
the syntax and it would have given you the above. You often get a lot more
info than you want from a recorded macro but you can extract the essential
part for your program.

If you don't know how to record then let me know what version of xl you are
using (ie xl2007 or earlier version) and I'll tell you how to get started.

Regards,

OssieMac

Thanks so much, OssieMac! One more thing. If I want to set the color black,
would I insert a line into your code like this?
.SeriesColeection(i).Border.ColorIndex = 0

I am totally ignorant about the color naming schemes. I went to the
pallette and looked around to get black and it seemed like RGB values were 0,
0, and 0. When I tried the line above, it changed all colors to something
besides black and also changed the line thicknesses. Do you have any
suggestions?
Thanks in Advance,
Raymondvillain
--
leave well enough alone


:

Hi,

Will something like this achieve your desired outcome. You don't need to
test when it is finished because .SeriesCollection.Count sets the number of
times to loop.

Set you border weight to whatever you want.

Sub Test()
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart
For i = 1 To .SeriesCollection.Count
.SeriesCollection(i).Border.Weight = xlThick
Next i
End With
End Sub

Regards,

OssieMac


:

I need to do the same thing to every series on a chart. Some of my charts
have 8 to 10 series lines drawn on them. I know how to write a loop, but I
don't know what the maximum number of SeriesCollection(?)'s is. I want to do
something like

While [what should the test be?]
ActiveChart.SeriesCollection(?).Border.Weight = 1
Wend

Does this make sense?

 

[Jon Peltier]

Excel 2007 has unfortunately lost much of its macro recording capability
with respect to charts, and all of it with respect to shapes. PowerPoint
2007 has lost macro recording altogether.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

Thanks so much for your help. Should have stated that am using Excel
2007.

I have recorded many macros over time, but in Excel 2007, there appears to
be a problem. I activate a chart, click on "record macro", then select a
series line, change linestyle, color, etc., click on "stop recording".
Then
when I open the macro in VBA, nothing is there but:

Sub new_demo()
'
' new_demo Macro
' Keyboard Shortcut: Ctrl+h
'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveSheet.ChartObjects("Chart 1").Activate
End Sub

What do you suggest? I could go back to Excel 97 on an older machine,
select chart, record macro, etc. and then open the workbook in Excel 2007
and
go from there.

Are there any reference books that you know of that list these features,
such as the black color being 1, etc.? I agree that recording a macro is
a
great way to figure out the intricacies of Visual Basic.

Thanks again for your time,
Raymondvillain
--
leave well enough alone

Hi again,

Black is ColorIndex 1. You have a typo in SeriesCollection but otherwise
you
are correct in the method.

Do you know how to record a macro? It is the greatest method of finding
out
the syntax and it would have given you the above. You often get a lot
more
info than you want from a recorded macro but you can extract the
essential
part for your program.

If you don't know how to record then let me know what version of xl you
are
using (ie xl2007 or earlier version) and I'll tell you how to get
started.

Regards,

OssieMac

Thanks so much, OssieMac! One more thing. If I want to set the color
black,
would I insert a line into your code like this?
.SeriesColeection(i).Border.ColorIndex = 0

I am totally ignorant about the color naming schemes. I went to the
pallette and looked around to get black and it seemed like RGB values
were 0,
0, and 0. When I tried the line above, it changed all colors to
something
besides black and also changed the line thicknesses. Do you have any
suggestions?
Thanks in Advance,
Raymondvillain
--
leave well enough alone


:

Hi,

Will something like this achieve your desired outcome. You don't need
to
test when it is finished because .SeriesCollection.Count sets the
number of
times to loop.

Set you border weight to whatever you want.

Sub Test()
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart
For i = 1 To .SeriesCollection.Count
.SeriesCollection(i).Border.Weight = xlThick
Next i
End With
End Sub

Regards,

OssieMac


:

I need to do the same thing to every series on a chart. Some of my
charts
have 8 to 10 series lines drawn on them. I know how to write a
loop, but I
don't know what the maximum number of SeriesCollection(?)'s is. I
want to do
something like

While [what should the test be?]
ActiveChart.SeriesCollection(?).Border.Weight = 1
Wend

Does this make sense?