Change colour of bars/lines in charts when data in rows/colums

[RodP]

Hi Everyone,

I'm putting together some vb code to change the colours of the bars or lines
on a chart in one go. I've set it up so far for when the data is in rows (ie
the individual series lie along the x axis). However, I need to consider how
to cycle through the data points for when it is in columns, which essentially
means I need to search for xlcategory names and the relavent points (I
think). I suppose I also need to consider other types of charts and so would
also be grateful for any guidance in how to best do this (as I presume you
can't use the 'interior' object for when using lines!).

Here's the code I've written which works fine for when data is in rows.
Below also is the data I've used.

-----------------------

Sub SetChartColoursForMults()

Dim Chartobj As Chart
Dim s As series
Dim pt As Point

On Error GoTo error_it
Chartname = ActiveChart.Name
On Error GoTo 0



Response = MsgBox("Please make sure you have selected the chart to change
the series colours." & Chr(10) _
& "The following Multiples will be set to the following [excel]
colours:-" & Chr(10) & Chr(10) _
& "Tesco Blue" & Chr(10) _
& "Sainsbury Orange" & Chr(10) _
& "Asda Bright Green" & Chr(10) _
& "Morrisons Black" & Chr(10) _
& "Safeway Red" & Chr(10) _
& "Somerfield Turquoise" & Chr(10) _
& "Kwik Save Pink" & Chr(10) _
& "Waitrose Green" & Chr(10) _
& "Iceland Tan" & Chr(10) _
& "C&I Gold", vbOKCancel, "Set Chart Colours for Multiples")
If Response = vbCancel Then Exit Sub

With ActiveChart

For Each s In .SeriesCollection
seriesname = s.Name

Select Case seriesname 'for when data is in rows
Case "Tesco": NewCol = 5 'Blue or could use vbblue
Case "Sainsbury": NewCol = 46 'Orange
Case "Asda": NewCol = 4 'Bright Green
Case "Morrisons": NewCol = 1 'Black
Case "Safeway": NewCol = 3 'Red
Case "Somerfield": NewCol = 28 'Turquoise
Case "Kwik Save": NewCol = 26 'Pink
Case "Waitrose": NewCol = 10 'Green
Case "Iceland": NewCol = 40 'Tan
Case "C&I": NewCol = 44 'Gold
End Select

With s.Interior
.ColorIndex = NewCol
.Pattern = xlSolid
End With

' plus need to check for columns when data is in coloumns
' hmmm how do i do this?!

Next

End With


Exit Sub

error_it:
Select Case Err
Case 91
MsgBox "Please select a chart!", vbOKOnly, "Set Chart Colours for
Multiples"
Case Else
MsgBox "There is a problem! Error code: " & Err & Chr(10) &
Err.Description
End Select

End Sub

-------------------

Data:

Multiples sales '04 sale '05
Tesco 23 34
Sainsbury 34 56
Asda 56 56
Morrisons 68 45
Safeway 65 23
Somerfield 32 34
Kwik Save 34 56
Waitrose 56 68
Iceland 56 65
C&I 45 32

Thanks in advance for your help.

<<<<<<<<<<<<STOP PRESS>>>>>>>>>>>>>

Hiya,

Update for you all,

I've managed to get a bit further on the code ie testing for xlrows /
xlcolumns by using the plotby command. However I'm still stuck on getting
the corresponding x axis category label / value for that particular point.
Can anyone help?

Many thanks

RodP

[Vacation's Over]

Not sure why the code posted would not work

these lines ignore where the data is row/col??
For Each s In .SeriesCollection
seriesname = s.Name

Issue is --> Where is the code to set series name?
that should be where the row/col issue is resolved

"RodP" wrote:

> Hi Everyone,
>
> I'm putting together some vb code to change the colours of the bars or lines
> on a chart in one go. I've set it up so far for when the data is in rows (ie
> the individual series lie along the x axis). However, I need to consider how
> to cycle through the data points for when it is in columns, which essentially
> means I need to search for xlcategory names and the relavent points (I
> think). I suppose I also need to consider other types of charts and so would
> also be grateful for any guidance in how to best do this (as I presume you
> can't use the 'interior' object for when using lines!).
>
> Here's the code I've written which works fine for when data is in rows.
> Below also is the data I've used.
>
> -----------------------
>
> Sub SetChartColoursForMults()
>
> Dim Chartobj As Chart
> Dim s As series
> Dim pt As Point
>
> On Error GoTo error_it
> Chartname = ActiveChart.Name
> On Error GoTo 0
>
>
>
> Response = MsgBox("Please make sure you have selected the chart to change
> the series colours." & Chr(10) _
> & "The following Multiples will be set to the following [excel]
> colours:-" & Chr(10) & Chr(10) _
> & "Tesco Blue" & Chr(10) _
> & "Sainsbury Orange" & Chr(10) _
> & "Asda Bright Green" & Chr(10) _
> & "Morrisons Black" & Chr(10) _
> & "Safeway Red" & Chr(10) _
> & "Somerfield Turquoise" & Chr(10) _
> & "Kwik Save Pink" & Chr(10) _
> & "Waitrose Green" & Chr(10) _
> & "Iceland Tan" & Chr(10) _
> & "C&I Gold", vbOKCancel, "Set Chart Colours for Multiples")
> If Response = vbCancel Then Exit Sub
>
> With ActiveChart
>
> For Each s In .SeriesCollection
> seriesname = s.Name
>
> Select Case seriesname 'for when data is in rows
> Case "Tesco": NewCol = 5 'Blue or could use vbblue
> Case "Sainsbury": NewCol = 46 'Orange
> Case "Asda": NewCol = 4 'Bright Green
> Case "Morrisons": NewCol = 1 'Black
> Case "Safeway": NewCol = 3 'Red
> Case "Somerfield": NewCol = 28 'Turquoise
> Case "Kwik Save": NewCol = 26 'Pink
> Case "Waitrose": NewCol = 10 'Green
> Case "Iceland": NewCol = 40 'Tan
> Case "C&I": NewCol = 44 'Gold
> End Select
>
> With s.Interior
> .ColorIndex = NewCol
> .Pattern = xlSolid
> End With
>
> ' plus need to check for columns when data is in coloumns
> ' hmmm how do i do this?!
>
> Next
>
> End With
>
>
> Exit Sub
>
> error_it:
> Select Case Err
> Case 91
> MsgBox "Please select a chart!", vbOKOnly, "Set Chart Colours for
> Multiples"
> Case Else
> MsgBox "There is a problem! Error code: " & Err & Chr(10) &
> Err.Description
> End Select
>
> End Sub
>
> -------------------
>
> Data:
>
> Multiples sales '04 sale '05
> Tesco 23 34
> Sainsbury 34 56
> Asda 56 56
> Morrisons 68 45
> Safeway 65 23
> Somerfield 32 34
> Kwik Save 34 56
> Waitrose 56 68
> Iceland 56 65
> C&I 45 32
>
> Thanks in advance for your help.
>
> <<<<<<<<<<<<STOP PRESS>>>>>>>>>>>>>
>
> Hiya,
>
> Update for you all,
>
> I've managed to get a bit further on the code ie testing for xlrows /
> xlcolumns by using the plotby command. However I'm still stuck on getting
> the corresponding x axis category label / value for that particular point.
> Can anyone help?
>
> Many thanks
>
> RodP
>

[RodP]

Hiya (err Vacation's Over - Hope you had a good time!),

Thanks for the reply. I've found that if the data in changed around so that
it is 'by column' then the series names changes to Sales '04 and Sales '05.
I'm unable to find out what the category name is for each point within the
series (ie. Tesco, Sainsbury, Asda etc.).

Do you have any other ideas?

Many thanks in advance

RodP

[Vacation's Over]

How did the values on the sheet get assigned to the chart?

Your posted code does not show the assignment

"RodP" wrote:

> Hiya (err Vacation's Over - Hope you had a good time!),
>
> Thanks for the reply. I've found that if the data in changed around so that
> it is 'by column' then the series names changes to Sales '04 and Sales '05.
> I'm unable to find out what the category name is for each point within the
> series (ie. Tesco, Sainsbury, Asda etc.).
>
> Do you have any other ideas?
>
> Many thanks in advance
>
> RodP
>

[RodP]

Hiya,

The chart was already produced, eg using chart wizard. For example, someone
else has put the data into excel and created the chart. I'm just coming
along to amend the formatting (hopefully with the click of a button!).

The code acts on the selected chart. If no chart is selected it tells the
user (by hook or by crook) to select a chart.

Hope this answers your question.

Cheers

Rodp

[Vacation's Over]

Garbage in = garbage out

If you let users chart whatever they want then you can not be sure that they
are charting the names correctly

if user clicks by col instead of by row, or just doesn't block the names as
well then your code CAN NOT work. You have written very specific code and
cannot let users use it as a utility.

"RodP" wrote:

> Hiya,
>
> The chart was already produced, eg using chart wizard. For example, someone
> else has put the data into excel and created the chart. I'm just coming
> along to amend the formatting (hopefully with the click of a button!).
>
> The code acts on the selected chart. If no chart is selected it tells the
> user (by hook or by crook) to select a chart.
>
> Hope this answers your question.
>
> Cheers
>
> Rodp
>

[RodP]

Hiya,

The users are pretty clued up on the creation of charts. Some charts need
to be charted by column aswell as by row - hence I need to cater for both
eventualities.

At the moment the code is rather specific as I'm not yet worrying about pie
charts etc. I'll come to that when I get this bit working.

All the charts so far have been created in quite a logical way (ie all data
is blocked together like in my example) although I didn't think that that
would have made any difference regarding my coding. Using a For each...next
loop, I'm able to cycle through all the bars when it's' by column' and 'by
row', it's just that I don't know how to find out what category the bar /
data point lies at when the data is plotted 'by column'. When 'by row' it's
simply the series name (s.name in my code).

Using said loop, I'm able to display the values for each point. Having
managed to do this, I thought it would be just as easy to pull out the
category also - but it's prooving more difficult by the sounds of it!

I'd be interested to know a little more about the problems you're talking
about - is my thought / idea a total no go as I should be approaching this
problem in another way? Any suggestions would be greatly welcomed!

Many thanks

RodP

[Vacation's Over]

I believe that your main goal is to have a unique color for a charted
"entity" and it can work. Safeway is always RED. the series.name should do
this as long as the user charts with Safeway showing up in a legend (as a
test, you do not need legend to be visible)

are you saying that if you block a range and chart it by col and then
transpose it and block and chart by row that the names shown in the legend
are still the same but that your code does not work?

I think that the issue you have is not in the posted code.

try stepping through the code in debugger with a watch on the s.name
or
insert a msgbox:

For Each s In .SeriesCollection
seriesname = s.Name

msgbox seriesname <=====insert this line to see what's going on

STOP THE PRESSES===============
just reread all posts:
What do you mean by "category name" for the Point

I thought you were controlling series color not point color & what is a
category?


"RodP" wrote:

> Hiya,
>
> The users are pretty clued up on the creation of charts. Some charts need
> to be charted by column aswell as by row - hence I need to cater for both
> eventualities.
>
> At the moment the code is rather specific as I'm not yet worrying about pie
> charts etc. I'll come to that when I get this bit working.
>
> All the charts so far have been created in quite a logical way (ie all data
> is blocked together like in my example) although I didn't think that that
> would have made any difference regarding my coding. Using a For each...next
> loop, I'm able to cycle through all the bars when it's' by column' and 'by
> row', it's just that I don't know how to find out what category the bar /
> data point lies at when the data is plotted 'by column'. When 'by row' it's
> simply the series name (s.name in my code).
>
> Using said loop, I'm able to display the values for each point. Having
> managed to do this, I thought it would be just as easy to pull out the
> category also - but it's prooving more difficult by the sounds of it!
>
> I'd be interested to know a little more about the problems you're talking
> about - is my thought / idea a total no go as I should be approaching this
> problem in another way? Any suggestions would be greatly welcomed!
>
> Many thanks
>
> RodP

[RodP]

Hiya,

I'll try and explain from the beginning again - but basically I'm wanting to
control point colour...

-I want to create a macro which formats the currently selected chart in a
set way.

Tesco = Blue
Asda = Green etc.

-There are a number of charts of varying types that we use within the
business including Column and Line charts
-Normally the data will look like this

Multiples sales '04 sale '05
Tesco 23 34
Sainsbury 34 56
Asda 56 56

-There is a need to plot the data 'by column' and 'by row'. When creating a
chart through the chart wizard button, after selecting the type of chart (eg
column (bar) chart) the wizard asks whether the series are in columns or
rows. This is the 'by column' / 'by row' thing I have been talking about in
previous emails.

- Selecting 'by rows' gives you the following x/y layout in a column chart

Sales value ¦ A S A
(y axis) ¦ S A T S A
¦ T S A T S A
¦ T S A T S A
-------¦------------------¦--------
sales '04 sales '05

sales (x axis)

Series legend: T = Tesco, S = Sainsbury, A = Asda

-Using my code - I am able to cycle through the names of the series (s.name)
easily and if s.name matches one of the cases (eg Tesco, Asda etc.), it
changes the colour of the bar accordingly.

- Selecting 'by column' gives you the following x/y layout in a column chart

Sales value ¦ B A B
(y axis) ¦ B A B A B
¦ A B A B A B
¦ A B A B A B
-------¦-------- --¦-------------¦--
Tesco Sainsbury Asda

(x axis)

Series legend: A = Sales '04, B = Sales '05

Using the code If you cycle through s.name now it now comes up with Sales
'04 and Sales '05 which is no good. And so I have written additional code
that cycles through each point of the series telling me (at the moment) the
colour of each point for each series. What I want though is not the colour
value but the x value (ie. the category value). Incidentally, when you place
the mouse over each point or the chart, a little box pops up displaying the
value of the point aswell as the x (or category) value - this is what I want
to get hold of through VB.

This is how i'm cycling through each point of each series at the moment:

Sub cyclethrupoints()
Dim pt As Point
Dim s As series

With ActiveChart
For Each s In .SeriesCollection
For Each pt In s.Points
pt.Select
MsgBox pt.Interior.Color
Next
Next
End With

End Sub


Hope this makes things a little more clearer in terms of what I am trying to
do and I hope you can help further.

Many thanks

RodP

[Vacation's Over]

Much closer:
(((((((using macro record to get name shown on a point
sample from help>Category Names Property

Charts("Chart1").Axes(xlCategory).CategoryNames = _
Array ("1985", "1986", "1987", "1988", "1989")
)))))))))

what you need to do is to cycle through the series names looking for
"Safeway" and if you do not find it then cycle through
Charts("yourchart").Axes(xlCategory).CategoryNames

in your series "cycle" you used the series OBJECT =>name property. here the
property CategoryNames returns an ARRAY so the mechanics are different.

((((Also from Macro Recorder to set color for point:
ActiveChart.SeriesCollection(1).Points(1). .ColorIndex = 3
)))))

cycle through each series in series collection
change color for the point that has the same index as the collection name
has in the array (I would switch all the Option Base1)

This is the how and it will work. If you need more help just let me know.

PS: this could come in handy so When it works how about posting back with
code

"RodP" wrote:

> Hiya,
>
> I'll try and explain from the beginning again - but basically I'm wanting to
> control point colour...
>
> -I want to create a macro which formats the currently selected chart in a
> set way.
>
> Tesco = Blue
> Asda = Green etc.
>
> -There are a number of charts of varying types that we use within the
> business including Column and Line charts
> -Normally the data will look like this
>
> Multiples sales '04 sale '05
> Tesco 23 34
> Sainsbury 34 56
> Asda 56 56
>
> -There is a need to plot the data 'by column' and 'by row'. When creating a
> chart through the chart wizard button, after selecting the type of chart (eg
> column (bar) chart) the wizard asks whether the series are in columns or
> rows. This is the 'by column' / 'by row' thing I have been talking about in
> previous emails.
>
> - Selecting 'by rows' gives you the following x/y layout in a column chart
>
> Sales value ¦ A S A
> (y axis) ¦ S A T S A
> ¦ T S A T S A
> ¦ T S A T S A
> -------¦------------------¦--------
> sales '04 sales '05
>
> sales (x axis)
>
> Series legend: T = Tesco, S = Sainsbury, A = Asda
>
> -Using my code - I am able to cycle through the names of the series (s.name)
> easily and if s.name matches one of the cases (eg Tesco, Asda etc.), it
> changes the colour of the bar accordingly.
>
> - Selecting 'by column' gives you the following x/y layout in a column chart
>
> Sales value ¦ B A B
> (y axis) ¦ B A B A B
> ¦ A B A B A B
> ¦ A B A B A B
> -------¦-------- --¦-------------¦--
> Tesco Sainsbury Asda
>
> (x axis)
>
> Series legend: A = Sales '04, B = Sales '05
>
> Using the code If you cycle through s.name now it now comes up with Sales
> '04 and Sales '05 which is no good. And so I have written additional code
> that cycles through each point of the series telling me (at the moment) the
> colour of each point for each series. What I want though is not the colour
> value but the x value (ie. the category value). Incidentally, when you place
> the mouse over each point or the chart, a little box pops up displaying the
> value of the point aswell as the x (or category) value - this is what I want
> to get hold of through VB.
>
> This is how i'm cycling through each point of each series at the moment:
>
> Sub cyclethrupoints()
> Dim pt As Point
> Dim s As series
>
> With ActiveChart
> For Each s In .SeriesCollection
> For Each pt In s.Points
> pt.Select
> MsgBox pt.Interior.Color
> Next
> Next
> End With
>
> End Sub
>
>
> Hope this makes things a little more clearer in terms of what I am trying to
> do and I hope you can help further.
>
> Many thanks
>
> RodP

[RodP]

Thanks Vacations' Over.

I'll look into your ideas and try to get it to work.

Cheers

RodP

"Vacation's Over" wrote:

> Much closer:
> (((((((using macro record to get name shown on a point
> sample from help>Category Names Property
>
> Charts("Chart1").Axes(xlCategory).CategoryNames = _
> Array ("1985", "1986", "1987", "1988", "1989")
> )))))))))
>
> what you need to do is to cycle through the series names looking for
> "Safeway" and if you do not find it then cycle through
> Charts("yourchart").Axes(xlCategory).CategoryNames
>
> in your series "cycle" you used the series OBJECT =>name property. here the
> property CategoryNames returns an ARRAY so the mechanics are different.
>
> ((((Also from Macro Recorder to set color for point:
> ActiveChart.SeriesCollection(1).Points(1). .ColorIndex = 3
> )))))
>
> cycle through each series in series collection
> change color for the point that has the same index as the collection name
> has in the array (I would switch all the Option Base1)
>
> This is the how and it will work. If you need more help just let me know.
>
> PS: this could come in handy so When it works how about posting back with
> code
>
> "RodP" wrote:
>
> > Hiya,
> >
> > I'll try and explain from the beginning again - but basically I'm wanting to
> > control point colour...
> >
> > -I want to create a macro which formats the currently selected chart in a
> > set way.
> >
> > Tesco = Blue
> > Asda = Green etc.
> >
> > -There are a number of charts of varying types that we use within the
> > business including Column and Line charts
> > -Normally the data will look like this
> >
> > Multiples sales '04 sale '05
> > Tesco 23 34
> > Sainsbury 34 56
> > Asda 56 56
> >
> > -There is a need to plot the data 'by column' and 'by row'. When creating a
> > chart through the chart wizard button, after selecting the type of chart (eg
> > column (bar) chart) the wizard asks whether the series are in columns or
> > rows. This is the 'by column' / 'by row' thing I have been talking about in
> > previous emails.
> >
> > - Selecting 'by rows' gives you the following x/y layout in a column chart
> >
> > Sales value ¦ A S A
> > (y axis) ¦ S A T S A
> > ¦ T S A T S A
> > ¦ T S A T S A
> > -------¦------------------¦--------
> > sales '04 sales '05
> >
> > sales (x axis)
> >
> > Series legend: T = Tesco, S = Sainsbury, A = Asda
> >
> > -Using my code - I am able to cycle through the names of the series (s.name)
> > easily and if s.name matches one of the cases (eg Tesco, Asda etc.), it
> > changes the colour of the bar accordingly.
> >
> > - Selecting 'by column' gives you the following x/y layout in a column chart
> >
> > Sales value ¦ B A B
> > (y axis) ¦ B A B A B
> > ¦ A B A B A B
> > ¦ A B A B A B
> > -------¦-------- --¦-------------¦--
> > Tesco Sainsbury Asda
> >
> > (x axis)
> >
> > Series legend: A = Sales '04, B = Sales '05
> >
> > Using the code If you cycle through s.name now it now comes up with Sales
> > '04 and Sales '05 which is no good. And so I have written additional code
> > that cycles through each point of the series telling me (at the moment) the
> > colour of each point for each series. What I want though is not the colour
> > value but the x value (ie. the category value). Incidentally, when you place
> > the mouse over each point or the chart, a little box pops up displaying the
> > value of the point aswell as the x (or category) value - this is what I want
> > to get hold of through VB.
> >
> > This is how i'm cycling through each point of each series at the moment:
> >
> > Sub cyclethrupoints()
> > Dim pt As Point
> > Dim s As series
> >
> > With ActiveChart
> > For Each s In .SeriesCollection
> > For Each pt In s.Points
> > pt.Select
> > MsgBox pt.Interior.Color
> > Next
> > Next
> > End With
> >
> > End Sub
> >
> >
> > Hope this makes things a little more clearer in terms of what I am trying to
> > do and I hope you can help further.
> >
> > Many thanks
> >
> > RodP