On Sun, 17 Dec 2006 07:17:01 -0800, ShaneDevenshire
wrote:

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...

--
Fredrik E. Nilsen