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

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

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

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

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

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