Colours in user-defined charts

[Fredrik E. Nilsen]

I have one more question about user-defined charts:

Is it possible to define the colours to be used? I can change the
colours in usrgal.xls but when I apply the user-defined chart, the
colurs revert to those in the current workbook.

Any way around this? The users can not be expected to import the
colours for every book and they have a lot of old books in wich they
are going to use the new colour-set.

 

[ShaneDevenshire]

Hi Fredrik,

As you have discovered the you can change the user defined colors for any
workbook but the pallete is workbook specific. If all the colors you want to
use are in the default pallete then you can create a User Defined custom
chart using any of those colors and they will be available in all workbooks
when you applie the User Defined chart. The downsides of this approach are
1. you must create a user defined chart for each of the chart types you use,
2. user defined charts don't retain all attributes so if the charts are fancy
you may loose some features such as linked titles or linked data lables, 3.
to apply the new colors used in the default chart requires a number of steps.
4. If all of the colors you want to use are not in the default pallete then
the above approach fails.

An easier way to deal with the problem might be to create a custom color
pallete for the personal macro workbook and add a macro that imports that
pallete into any workbook. Assuming you have created the custom pallete in
the Personal Macro Workbook then the code you need to add to the PMW is very
simple:

Sub CustomChartColors()
' Keyboard Shortcut: Ctrl+Shift+C

ActiveWorkbook.Colors = Workbooks("PERSONAL.XLS").Colors
End Sub

The shortcut key is optional.

Alternately you could create a macro that redefines the colors of the
pallete in any workbook and place that macro in the personal macro workbook.

With either of these two approaches you will need to add the macro or the
macro and the pallete to the personal marco workbook of each user. Of course
you don't need to use the personal macro workbook, you could use an XLA. The
advantage of an XLA is that you don't need to mess with the PMW which may
already have thing in it. And a single copy of the XLA can live on a server.

Sincerely,
Shane Devenshire

 

[Fredrik E. Nilsen]

Hi Fredrik,

As you have discovered the you can change the user defined colors for any
workbook but the pallete is workbook specific. If all the colors you want to
use are in the default pallete then you can create a User Defined custom
chart using any of those colors and they will be available in all workbooks
when you applie the User Defined chart. The downsides of this approach are
1. you must create a user defined chart for each of the chart types you use,
2. user defined charts don't retain all attributes so if the charts are fancy
you may loose some features such as linked titles or linked data lables, 3.
to apply the new colors used in the default chart requires a number of steps.
4. If all of the colors you want to use are not in the default pallete then
the above approach fails.

An easier way to deal with the problem might be to create a custom color
pallete for the personal macro workbook and add a macro that imports that
pallete into any workbook. Assuming you have created the custom pallete in
the Personal Macro Workbook then the code you need to add to the PMW is very
simple:

Sub CustomChartColors()
' Keyboard Shortcut: Ctrl+Shift+C

ActiveWorkbook.Colors = Workbooks("PERSONAL.XLS").Colors
End Sub

The shortcut key is optional.

Alternately you could create a macro that redefines the colors of the
pallete in any workbook and place that macro in the personal macro workbook.

With either of these two approaches you will need to add the macro or the
macro and the pallete to the personal marco workbook of each user. Of course
you don't need to use the personal macro workbook, you could use an XLA. The
advantage of an XLA is that you don't need to mess with the PMW which may
already have thing in it. And a single copy of the XLA can live on a server.

Sincerely,
Shane Devenshire

Thank you very much for your reply, this clarifies a lot. In this
case, I guess it would make sense to define the colours in
xlusrgal.xls and to refer to that in the code sample you provided.

A couple of more questions then comes to mind: If I apply your code
this affects all charts in the workbook. This is not necessarily a
problem but is there a way to affect only the selected chart?

Regarding your first paragraph, I'm aware of the problems regarding
user-defined charts not retaining all attributes. It is also
impossible to know what kind of changes they are going to try to make
so no matter how detailed my user-defined charts are, at one point my
settings will fail.

The charts are not going to be all that fancy really, the real problem
is that there are too many of them to format manually. What I would
like ideally was a button to apply all my settings on the selected
chart, no matter how many lines, columns, axis etc.

With "my settings" I mean: Colours, Line thickness, No legend, No axis
names, Fixed textsizes, No background colour, White frame colour etc.
Any simple way to do this? The users are experts in the financial
system but really useless when it comes to Excel...

 

[ShaneDevenshire]

Hi Fredrik,

To the best of my knowledge you can only apply colors from the current
workbook's color pallete and the color is referenced not by its color but by
its position.

If you use my suggestion of the XLA macro you can put the XLA on a server
and tell each user how to attach it. Once attached all they will need to do
in the future is press the shortcut key whenever they open an old file with
chart which they want to have updated to the new color scheme. It's a one
time process for each file, not each chart. If you need to know how to make
the file an xla and attach it, let me know.

 

[Fredrik E. Nilsen]

Hi Fredrik,

To the best of my knowledge you can only apply colors from the current
workbook's color pallete and the color is referenced not by its color but by
its position.

If you use my suggestion of the XLA macro you can put the XLA on a server
and tell each user how to attach it. Once attached all they will need to do
in the future is press the shortcut key whenever they open an old file with
chart which they want to have updated to the new color scheme. It's a one
time process for each file, not each chart. If you need to know how to make
the file an xla and attach it, let me know.

Thanks again Shane. I have already made the XLA and made a toolbar
with all the user-defined charts, it works like a charm but with the
limitations mentioned in you earlier post.

 

[ShaneDevenshire]

If you want to apply these on a chart by chart basis then the real approach
is to develop code for all the changes you want - line thickness, axis,
colors and so on. Run that code from an xla just as described earlier. This
is safer than applying a user defined chart because of the problems I
mentioned. You code will require that the user select the chart and then
execute the code. As for color, if the color is from the default pallete,
you might as well include it in the macro rather than changing the pallete.
When you change the pallete you effect everything that uses it not just all
charts.

Regards,
Shane

 

[Fredrik E. Nilsen]

If you want to apply these on a chart by chart basis then the real approach
is to develop code for all the changes you want - line thickness, axis,
colors and so on. Run that code from an xla just as described earlier. This
is safer than applying a user defined chart because of the problems I
mentioned. You code will require that the user select the chart and then
execute the code. As for color, if the color is from the default pallete,
you might as well include it in the macro rather than changing the pallete.
When you change the pallete you effect everything that uses it not just all
charts.

Thanks again, I really appreciate the time you take to explain it in
an understandable way.

Do you have any pointers regarding coding all the changes? I'm in a
bit over my head here but I have basic knowledge about VB. In "real"
english it would be something like:

On the selected chart (no matter what type of chart it is):
Change all lines to... (thickness, markings etc)
Change all columns to... (no border etc)
Set textsize to...
Set legend placement to...
Set colours to...
etc....

The colour issue is not really a problem in this particular case,
though I see your point. But: If I pull the colours from chart fill
and chart line colours in xlusrgal.xls, that won't affect the standard
colours? I see that it will affect the last 16 colours of the palettes
but thats not a problem, it's a bonus feature.

The users in this case are financial analysts and they make huge
reports in Word every day. They pull charts from Excel in to Word and
want to achieve consistant formatting throughout all the charts.

 

[ShaneDevenshire]

I always recommend starting with the recorder. Then looking at the code and
deciding what needs to be generalized. This can be pretty complex.

I would make the recording on the most complex chart I have to modify and
then work from there.

I would also recommend that you check out Jon Peltier website since he has
lots of code and examples http://PeltierTech.com.

 

[Jon Peltier]

A couple of more questions then comes to mind: If I apply your code
this affects all charts in the workbook. This is not necessarily a
problem but is there a way to affect only the selected chart?

Shane's code affects the color palette in effect for the entire workbook.
Therefore it affects every chart and every worksheet in the workbook.

- Jon

 

[Jon Peltier]

The colour issue is not really a problem in this particular case,
though I see your point. But: If I pull the colours from chart fill
and chart line colours in xlusrgal.xls, that won't affect the standard
colours? I see that it will affect the last 16 colours of the palettes
but thats not a problem, it's a bonus feature.

If you pull the color palette from the user gallery workbook (i.e., copy the
color palette), it will affect all 56 colors of the palette.

If you pull the colors from the chart element colors of the user gallery
(i.e., apply the user-defined chart types), it will not affect any of the
palette colors in the target workbook. The color index of each chart element
will use the color index (position in the palette) of the element in the
user gallery, but not necessarily the same color if the palettes are not the
same.

- Jon