Solved: Distributing Color Palette Changes

[shades]

Howdy. I have written code that changes the 56-Color Palette to reflect our company designated colors (for fonts, background, and charts). It works very well - I have a button to activate for the current workbook. However, to insure that others who open the workbook can see/use the same color scheme, I have copied the code into the Workbook_Open event.

That is workable but seems like extra steps. What is the best way to automatically add the code to the Workbook_Open event? My goal is that others who use the code will then be able to use the same for any workbook they develop - so that others may use the same scheme.

[XLGibbs]

Is the code both in the workbook open event and elsewhere? I would think you can just call the one piece of code from the open event rather than duplicating it there....

[johnske]

...That is workable but seems like extra steps. What is the best way to automatically add the code to the Workbook_Open event? My goal is that others who use the code will then be able to use the same for any workbook they develop - so that others may use the same scheme.
What about saving it as a template? (They then have a choice).

Most would probably say make it an add-in, but personally I absolutely detest add-ins because every time I open any workbook from then on it takes forever to access the workbook due to having to load all the add-ins. The only add-in I use now is the 'smart indent' one. :)

[shades]

Thanks, guys. This is partially (mostly) solved. I tried it a couple of ways and found XL Gibbs' suggestion the best. Thus, in the Workbook_Open module, I have a one-line piece of code that calls the standard Color module. This way, all new spreadsheets begin with the new color scheme. I still have to go manually into a workbook I receive from someone else to enter the code into that workbook. But this is a step forward for others.

EDIT: Not as close as I wanted. When I first open Excel, the new worksheet will have the color scheme (from Personal.xls Workbook_Open method). But if I open a new xls file from File > Open, or click on the New button on toolbar, it does not have the new color scheme.

So, now back to re-thinking.

[johnske]

Actually,

What I had in mind was code for a workbook open event for the adding the colours, but then save this as a template i.e. something along these lines which is then saved as templateOption Explicit

Private Sub Workbook_Open()

'put your own path below
Const PathToColours As String = "sketches.xls"
'put your own refresh interval below
Const RefreshInterval As Long = 7 '< i.e. update every seven days

If Now - ActiveWorkbook.BuiltinDocumentProperties("Last Save Time") >= RefreshInterval Then
On Error Resume Next '< error = book already open
'insert the path to the
Workbooks.Open (PathToColours)
On Error GoTo Msg '< error = PathToColours can't be found
ActiveWorkbook.Colors = Workbooks(PathToColours).Colors
Workbooks(PathToColours).Close
End If
Exit Sub

Msg:
MsgBox "Cannot find " & PathToColours & " to refresh the colour pallette.." & vbNewLine & _
vbNewLine & _
"To maintain colour compatibility it is advisable that you" & vbNewLine & _
"close this workbook, find " & PathToColours & " and place it in" & vbNewLine & _
"this workbooks path before reopening it.", vbInformation, "Advisory Message..."
End Sub

[shades]

Thanks, John. I couldn't get your code to work properly - when you are as old as I am, sometimes nothing works like it supposed to. However, the idea of a template caused me to setup a template with the color palette already set with the new color scheme. Thus, a person can choose "New" and select the template. So, it achieves the purpose without the code.