Create and save a custom theme colors palette

User avatar
ErikJan
5StarLounger
Posts: 1185
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Create and save a custom theme colors palette

Post by ErikJan »

I know there are themes in MS365 that once can create / edit and then save and apply. I'd like to create some code (e.g. in Excel) that changes the 12 theme colors (with specific RGB values) only and then saves under a certain name. They will then become available on the system that runs the code for all MS365 Apps.
Can someone get me started with some VBA code here? I searched but couldn't quickly find something yet.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Create and save a custom theme colors palette

Post by HansV »

This was frustrating. All examples provided by Microsoft are completely incorrect. I eventually found an example elsewhere that pointed in the right direction.

Code: Select all

Sub ChangeThemeColors()
    Dim tTheme As OfficeTheme
    Dim tcThemeColorScheme As ThemeColorScheme
    Set tTheme = ActiveWorkbook.Theme
    Set tcThemeColorScheme = tTheme.ThemeColorScheme
    With tcThemeColorScheme
        .Colors(msoThemeLight1).RGB = RGB(128, 255, 0)
        .Colors(msoThemeLight2).RGB = RGB(0, 128, 255)
        .Colors(msoThemeDark1).RGB = RGB(128, 128, 128)
        .Colors(msoThemeDark2).RGB = RGB(255, 128, 0)
        .Colors(msoThemeAccent1).RGB = vbRed
        .Colors(msoThemeAccent2).RGB = vbBlue
        .Colors(msoThemeAccent3).RGB = vbYellow
        .Colors(msoThemeAccent4).RGB = vbGreen
        .Colors(msoThemeAccent5).RGB = vbCyan
        .Colors(msoThemeAccent6).RGB = vbMagenta
    End With
End Sub
Best wishes,
Hans

User avatar
Guessed
2StarLounger
Posts: 102
Joined: 04 Feb 2010, 22:44
Location: Melbourne Australia

Re: Create and save a custom theme colors palette

Post by Guessed »

Hans' code is editing the active workbook's theme but misses a couple of color slots and the step of saving the theme colors so they can be selected in other MS Office apps or files. Those missing steps are included in this (Word-specific) snippet.

Code: Select all

  With ActiveDocument.DocumentTheme
    .ThemeColorScheme(msoThemeDark1).RGB = 0                      'Black
    .ThemeColorScheme(msoThemeLight1).RGB = RGB(255, 255, 255)    'White
    .ThemeColorScheme(msoThemeDark2).RGB = RGB(65, 64, 66)        'Charcoal
    .ThemeColorScheme(msoThemeLight2).RGB = RGB(75, 207, 174)     'Mint
    .ThemeColorScheme(msoThemeAccent1).RGB = RGB(92, 116, 132)    'Blue Grey
    .ThemeColorScheme(msoThemeAccent2).RGB = RGB(110, 194, 197)   'Teal
    .ThemeColorScheme(msoThemeAccent3).RGB = RGB(245, 121, 53)    'Sol
    .ThemeColorScheme(msoThemeAccent4).RGB = RGB(212, 20, 90)     'Magenta
    .ThemeColorScheme(msoThemeAccent5).RGB = RGB(34, 181, 115)    'Leaf Green
    .ThemeColorScheme(msoThemeAccent6).RGB = RGB(42, 187, 224)    'Azure
[b]    .ThemeColorScheme(msoThemeHyperlink).RGB = RGB(52, 152, 219)  'Sky Blue
    .ThemeColorScheme(msoThemeFollowedHyperlink).RGB = RGB(100, 49, 141)    'Grape
    .ThemeColorScheme.Save FileName:="MyColorTheme.xml"    'Creates/Overwrites[/b]
  End With
Once the color theme has been created as a standalone color theme, you can apply it to other files either from the Design > Colors or via code

Code: Select all

Sub ApplyColourTheme()			'this one is a Word macro
  Dim sPath As String
  sPath = Application.Options.DefaultFilePath(wdUserTemplatesPath) & "\Document Themes\Theme Colors\MyColorTheme.xml"
  Debug.Print sPath
  ActiveDocument.DocumentTheme.ThemeColorScheme.Load (sPath)
End Sub
Andrew Lockton
Melbourne Australia

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Create and save a custom theme colors palette

Post by HansV »

Thanks!
Best wishes,
Hans

User avatar
ErikJan
5StarLounger
Posts: 1185
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Create and save a custom theme colors palette

Post by ErikJan »

Thanks to both of you! I'll give this a try.

To build on the last code snippet, which -I think- applies the theme for Word. It would be great to extend that same piece of code to apply the theme just saved also for PowerPoint, Access, Outlook and Excel (I'd like all the code in Excel for my liking but anything goes I guess).

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Create and save a custom theme colors palette

Post by HansV »

Once the theme has been saved as a XML file, all Office applications should be able to use it.
The code to apply it to the active Excel workbook:

Code: Select all

Sub ApplyColourTheme() ' for Excel
    Dim sPath As String
    sPath = Application.TemplatesPath & "\Document Themes\Theme Colors\MyColorTheme.xml"
    ActiveWorkbook.Theme.ThemeColorScheme.Load sPath
End Sub
I can't help you with the code for Access, PowerPoint or Outlook.
Best wishes,
Hans

User avatar
ErikJan
5StarLounger
Posts: 1185
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Create and save a custom theme colors palette

Post by ErikJan »

Right, thanks Hans. What I was hoping was that I can end up with two routines in VBA (e.g. in Excel).
The first one is above and creates and saves the color template (I'll test that).
The second one applies that template (from the Excel VBA routine) to Excel itself, then WORD, PowerPoint, Outlook and Access. We have the WORD code to do that, I can try to convert that so it works from Excel VBA

User avatar
ErikJan
5StarLounger
Posts: 1185
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Create and save a custom theme colors palette

Post by ErikJan »

By the way... converting the first code (defining the theme) to Excel is not trivial it seems. Is that what you stumbled upon too Hans?

This comes closer I hope (from MS):

Dim tTheme As OfficeTheme
Dim tcsThemeColorScheme As ThemeColorScheme
Dim tcThemeColor As ThemeColor
tcThemeColor.RGB = RGB(255, 0, 0)
Set tcColorScheme.Colors(msoThemeAccent1) = tcThemeColor
tcsThemeColorScheme.Save ("C:\myThemeColorScheme.xml")

Sorry for the edits... got this part for Excel... starts with: With ActiveWorkbook.Theme

User avatar
Guessed
2StarLounger
Posts: 102
Joined: 04 Feb 2010, 22:44
Location: Melbourne Australia

Re: Create and save a custom theme colors palette

Post by Guessed »

The first macro does two things:
1. customises the colour theme in the current workbook
2. saves this as a re-usable colour theme (xml file in a specific folder on your hard drive)

Once the xml file is created and in the right location, you can apply that colour theme to any office document or template (not to Visio though). You don't need to recreate the xml file in every application. If you want to apply the colour theme, you can apply it via the GUI or via code.

The second macro applies an existing 'exported' colour theme to an existing file. By its nature, it needs to be application specific.

Other background info that you may or may not already know:
Office docs/workbooks with the theme attached don't need the xml also saved on the machine as a copy of the theme is embedded in the file itself - but on other machines, the user won't be able to apply that theme to other documents unless they have also saved the xml file to their special folder location. Note that in Powerpoint, there can be multiple colour themes if you have multiple masters so applying a colour theme is trickier in that software.

Tweaking both macros for Excel and sharing the xml name as a constant gives us:

Code: Select all

Private Const sThemeName As String = "MyColorTheme2.xml"

Sub CreateColorTheme()
  'Customises the color theme applied to current workbook and then saves it to the user's theme folder
  Dim sPath As String
  sPath = Application.TemplatesPath & "\Document Themes\Theme Colors\"
  With ActiveWorkbook.Theme
    .ThemeColorScheme(msoThemeDark1).RGB = 0                      'Black
    .ThemeColorScheme(msoThemeLight1).RGB = RGB(255, 255, 255)    'White
    .ThemeColorScheme(msoThemeDark2).RGB = RGB(65, 64, 66)        'Charcoal
    .ThemeColorScheme(msoThemeLight2).RGB = RGB(75, 207, 174)     'Mint
    .ThemeColorScheme(msoThemeAccent1).RGB = RGB(92, 116, 132)    'Blue Grey
    .ThemeColorScheme(msoThemeAccent2).RGB = RGB(110, 194, 197)   'Teal
    .ThemeColorScheme(msoThemeAccent3).RGB = RGB(245, 121, 53)    'Sol
    .ThemeColorScheme(msoThemeAccent4).RGB = RGB(212, 20, 90)     'Magenta
    .ThemeColorScheme(msoThemeAccent5).RGB = RGB(34, 181, 115)    'Leaf Green
    .ThemeColorScheme(msoThemeAccent6).RGB = RGB(42, 187, 224)    'Azure
    .ThemeColorScheme(msoThemeHyperlink).RGB = RGB(52, 152, 219)     'Sky Blue
    .ThemeColorScheme(msoThemeFollowedHyperlink).RGB = RGB(100, 49, 141)    'Grape
    .ThemeColorScheme.Save Filename:=sThemeName    'Creates/Overwrites
  End With
  MsgBox sPath & sThemeName, vbOKOnly, "Color Theme Created"
End Sub

Sub ApplyColourTheme() ' for Excel
  Dim sPath As String
  sPath = Application.TemplatesPath & "\Document Themes\Theme Colors\" & sThemeName
  ActiveWorkbook.Theme.ThemeColorScheme.Load sPath
End Sub
Andrew Lockton
Melbourne Australia

User avatar
ErikJan
5StarLounger
Posts: 1185
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Create and save a custom theme colors palette

Post by ErikJan »

Yep, OK, I got that and I can create the XML file.

It shows up in Page Layout / Colors (at the top under Custom) and I see it as template used for the sheet.
Pic1.png
Some first questions (still talking Excel only for now):
1. I'd like this theme to become the default for all new workbooks. How do I set that (pref. via VBA)?
2. Oddly enough, the ThemeNames don't match the pop-up names, i.e. "msoThemeLight2" pops up as "Background 2". What am I missing?
3. The colors in the popup are named (e.g. I defined to Red's and they are both called "Red"), yet another I defined a 'sage' (a greyish color) and it pops up as "Green"... So how do I set / control the color names that pop-up?

Update: What'[s weird too (?) is that the palette display changes in the "Home" ribbon...
Pic2.png
You do not have the required permissions to view the files attached to this post.

User avatar
Guessed
2StarLounger
Posts: 102
Joined: 04 Feb 2010, 22:44
Location: Melbourne Australia

Re: Create and save a custom theme colors palette

Post by Guessed »

1. You could save a blank workbook in which you have already applied the theme on as the Book.xltx file (see https://www.youtube.com/watch?v=hU1Yx9Hej8w). The second macro above applies the colour theme to the current workbook. Alternatively, you might run that on every workbook you open so it would apply that theme on the fly. You can make macros run on workbook actions like when opening - see https://support.microsoft.com/en-us/off ... 017600db44

2. You aren't missing anything - Microsoft are not sticklers for consistency if they think no-one is looking. In my mind (and apparently in MS programmers minds) Light 1 and 2 are theme colours for backgrounds and Dark 1 and 2 are theme colours for text. The fact that they interchange these terms with their internal naming is odd but I can see how it might happen.

3. I suspect there is no way you or I can name the colours in the theme palette. I think the colour name that pops up appears to be autogenerated by Microsoft based on the rgb proportions. If it bothers you enough to explore, you could try inserting attributes into the theme's xml file after it has been created. See below for some clues on how you might do this...

There is ANOTHER place that you can implement custom colours - see https://www.brandwares.com/bestpractice ... om-colors/. The benefits of this extract hack is that you can name the colours explicitly and they don't change as the colour theme changes.
Andrew Lockton
Melbourne Australia

User avatar
ErikJan
5StarLounger
Posts: 1185
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Create and save a custom theme colors palette

Post by ErikJan »

Thanks again!

1. So if I don't want templates or macro's, but just want the default color template replaced (say for a company to ensure all use the same setup), that's not possible then?
2. OK... pity it's like this
3. OK... weird it's like this... Would be so nice to name the colors you define

The alternative approach seems an option but that's too complex probably (also as I want this for all Office).

User avatar
Guessed
2StarLounger
Posts: 102
Joined: 04 Feb 2010, 22:44
Location: Melbourne Australia

Re: Create and save a custom theme colors palette

Post by Guessed »

Making this available for an entire company is essentially NOT possible without some combination of templates / macros / IT administration - there are many components that need to be in place.
1. The color theme needs to applied to the templates that your users access when they create new Word, Excel, Powerpoint files. These can be created on one machine and then rolled out to all users by your IT administrators
2. The xml should also be rolled out to all users if you want them to have the ability to select this theme for existing files. This can either get onto machines by IT rolling it out or by including a macro in one of the templates and having the macro run on each machine
Andrew Lockton
Melbourne Australia

User avatar
ErikJan
5StarLounger
Posts: 1185
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Create and save a custom theme colors palette

Post by ErikJan »

OK, thanks again. I was hoping that by using one VBA macro, we could 'color-brand' all Office Apps.
The above discussion was about step 1: creating and saving the XML [and that works]
Step 2 would have been to replace the default Excel color palette with this new one, in such a way that from that moment forward Excel uses that palette (without the need for macro's and/or templates) [not possible I understood]
Step 3 would have been to allow the Excel code to also take care of the default color palette in Word, PowerPoint and Access (irrelevant if #2 is not possible]

Ah well... it is what it is... Thanks for the help. Appreciated :)