Solved Changing Color Theme via VBA in Runtime

[Pac-Man]

Hello,

There is a color option from which we can change whole color scheme of our database. My question is, can we change that color scheme in runtime by having a combo box on a form to change the theme?

Best Regards

 

[Pat Hartman]

Probably. If you can, someone will post the code but it is more difficult than that. The themes are not interchangeable. The fonts on them are different and depending on what theme you developed with, switching to one with a different font can truncate the text in labels and controls. If you are going to do this, you will need to work out ahead of time, which alternate themes can be used without messing up the text on your forms and reports and restrict the choice to just those themes that won't break anything.

 

[Pac-Man]

Thanks for reply @Pat Hartman. Color option m referring to is shown in attached image. Just want to have have ability to change colors while in runtime. Fonts are not being changed.

 

[MajP]

May want to look at this Theme Manager

 

[isladogs]

Suggest you look at this: ThemeMyDatabase

 

[Pac-Man]

Thanks a lot for reply @MajP @isladogs. By going through the links, it seems that changing color scheme provided in access ribbon however alternate method are there. I will look into and try to apply the method shared in the db shared in the posted links.
Regards

 

[isladogs]

For info, the utility created by DaveGri (post #4 link) doesn't actually use the built-in Access theme feature though it has similar results
Peter Cole's utility (link in post #5) does use Access themes

 

[Pat Hartman]

Be careful about allowing the user to customize the FE unless you are storing the customizations in a table so they can be reapplied. Otherwise, when you replace the FE with an updated version, the user's customizations will be lost.

 

[Pac-Man]

For info, the utility created by DaveGri (post #4 link) doesn't actually use the built-in Access theme feature though it has similar results
Peter Cole's utility (link in post #5) does use Access themes

I've checked the link in post 4 and I found out that it cannot be used in db since it changes all buttons color to same color. I have different buttons with different colors. If changed it to other color scheme (using acces theme), each button's color is changed to respective accent and that is what I want.

Be careful about allowing the user to customize the FE unless you are storing the customizations in a table so they can be reapplied. Otherwise, when you replace the FE with an updated version, the user's customizations will be lost.

Yes, selected value will be saved locally and will not be replaced with FE update.

I found (here and here) that xml file of scheme can be generated and loaded on demand but that code is for excel and is not working on Access. Can you please guide how can I use this in Access?

Edit: I verified the code on second link (code shown below) in excel and it is working the way exactly I need but can't figure out how can I make it work in Access.

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

Regards

 

[isladogs]

Did you try the link I provided in post #5?

 

[Pac-Man]

Did you try the link I provided in post #5?

Yes I saw it. But is accde and couldn't figure aout how to ise this in my db. It adds theme but I think one have to change theme using access ribbon which in my db are hidden along with the access window.

 

[Pac-Man]

I am surprised that one thing that is common between excel and access but in excel it can be performed using vba but not in access. There is no equivalent code for ActiveWorkbook.Theme.

 

[Pat Hartman]

Try the code with an .accdb. If it works, then you know you cannot use it in an .accde.

 

[Pac-Man]

Try the code with an .accdb. If it works, then you know you cannot use it in an .accde.

Yes I tried it in accdb but ActiveWorkbook code did not work in access file.

 

[Pat Hartman]

Guess I'm not sure how Excel got into this discussion. I thought we were talking about Access. I would not expect ActiveWorkbook to work in Access since that is Excel syntax.

 

[Pac-Man]

Guess I'm not sure how Excel got into this discussion. I thought we were talking about Access. I would not expect ActiveWorkbook to work in Access since that is Excel syntax.

Sorry for confusion. The code I posted in my former post was taken from excel forum and that code works perfectly but I cannot convert it to Access. I thought you're referring to that code in your following post

Try the code with an .accdb. If it works, then you know you cannot use it in an .accde.

If you're not referring to that code I posted then please tell me which code are you referring to so that I can try that.

 

[Pat Hartman]

I would never do this so I don't have any code to offer. Letting the user make design changes to the FE is poor practice unless you are prepared to save the changes in a table so you can reapply them every time the database opens.

The others offered code. You said you tried it in an .accde. I said to try (whatever code you tried in the .accde) in an .accdb. Code that affects object design rarely works in an .accde so running it in an .accdb would clarify the issue.

 

[petertheme]

From post #9 I see that you appreciate how different buttons can use different Accent numbers to give the different colors.
Access stores themes in the hidden system table MsysResources as an Attachment unlike Excel Word etc that store the XML of the theme.
in XML text.

Each time you select a theme from the ribbon Theme Gallery Access adds an entry to the MSysResources table. If the Theme has already been stored it will be prefixed with a number followed by underscore. eg The first use of Facet will have the name Facet, selecting again will add the name 1_Facet

The names will remain in MSysResources until a compact and repair, eliminating all but the last theme.

The property to be read or set is
CurrentDb.Properties("Theme Resource Name")

The spaces are required.

Reading the property will show the current Theme name

If the property is set to a new theme name and you close and reopen the database the new theme colors will show if that theme name exists in MSysresources. If that theme has never been used or the db has been compacted there will be an error.
To prevent the error the theme would need to be added to MSysResources. As the theme is stored in an attachment field you have to use VBA, you can't use an SQL Append on attachments.

The following code will add Slice to MSysResources.
(If running Access 2010 or 2013 you will need to adjust the 16.

Public Sub TESTAddNewThemeToMSysResources()

  AddNewThemeToMSysResources "C:\Program Files\Microsoft Office\root\Document Themes 16\Slice.thmx"
End Sub

Public Sub AddNewThemeToMSysResources(strNewThemeFullName As String)

Dim rst As DAO.Recordset
Dim rstNewThemeData As DAO.Recordset2
Dim strExt As String

Dim strNewThemeName As String
Dim lngPosBS As Long

 
  strNewThemeName = Replace(Mid(strNewThemeFullName, InStrRev(strNewThemeFullName, "\") + 1), ".thmx", "")
  Debug.Print strNewThemeName
  Stop
  Set rst = CodeDb.OpenRecordset("SELECT * FROM MSysResources WHERE 1 = 0")

  rst.AddNew
  rst.Update
  rst.Bookmark = rst.LastModified
  rst.Edit
  rst!Extension = "thmx"
  rst!Name = strNewThemeName
  rst!Type = "thmx"
 
  Set rstNewThemeData = rst.Fields("Data").Value
  rstNewThemeData.AddNew
  rstNewThemeData.Fields("FileData").LoadFromFile strNewThemeFullName
  rstNewThemeData.Update
  rst.Update
  rst.Close

End Sub

This works for the standard themes but if allowing users to change the applied theme you will need to create custom themes using the fonts that you use in your database otherwise you might have the "Fit" problem for text that Pat describes.

It is easy to create custom themes with different fonts using my Free TMDThememaker.

Custom themes are stored in the users profile
C:\Users\usersname\AppData\Roaming\Microsoft\Templates\Document Themes
They are XML so you could store a copy of each custom theme in a table in your database then on opening the form that allows the user to change theme copy the XML to the Documents Themes folder then add the file as the attachment using a modified version of the above.

For others reading this post, this code is only needed as the Ribbon is not available.

This works in accde.

 

[Pac-Man]

From post #9 I see that you appreciate how different buttons can use different Accent numbers to give the different colors.
Access stores themes in the hidden system table MsysResources as an Attachment unlike Excel Word etc that store the XML of the theme.
in XML text.

Each time you select a theme from the ribbon Theme Gallery Access adds an entry to the MSysResources table. If the Theme has already been stored it will be prefixed with a number followed by underscore. eg The first use of Facet will have the name Facet, selecting again will add the name 1_Facet

The names will remain in MSysResources until a compact and repair, eliminating all but the last theme.

The property to be read or set is
CurrentDb.Properties("Theme Resource Name")

The spaces are required.

Reading the property will show the current Theme name

If the property is set to a new theme name and you close and reopen the database the new theme colors will show if that theme name exists in MSysresources. If that theme has never been used or the db has been compacted there will be an error.
To prevent the error the theme would need to be added to MSysResources. As the theme is stored in an attachment field you have to use VBA, you can't use an SQL Append on attachments.

The following code will add Slice to MSysResources.
(If running Access 2010 or 2013 you will need to adjust the 16.


Public Sub TESTAddNewThemeToMSysResources()

AddNewThemeToMSysResources "C:\Program Files\Microsoft Office\root\Document Themes 16\Slice.thmx"
End Sub



Public Sub AddNewThemeToMSysResources(strNewThemeFullName As String)

Dim rst As DAO.Recordset
Dim strNewThemeData As DAO.Recordset2
Dim strExt As String

Dim strNewThemeName As String
Dim lngPosBS As Long


strNewThemeName = Replace(Mid(strNewThemeFullName, InStrRev(strNewThemeFullName, "\") + 1), ".thmx", "")
Debug.Print strNewThemeName
Stop
Set rst = CodeDb.OpenRecordset("SELECT * FROM MSysResources WHERE 1 = 0")

rst.AddNew
rst.Update
rst.Bookmark = rst.LastModified
rst.Edit
rst!Extension = "thmx"
rst!Name = strNewThemeName
rst!Type = "thmx"

Set strNewThemeData = rst.Fields("Data").Value
strNewThemeData.AddNew
strNewThemeData.Fields("FileData").LoadFromFile strNewThemeFullName
strNewThemeData.Update
rst.Update
rst.Close

End Sub

This works for the standard themes but if allowing users to change the applied theme you will need to create custom themes using the fonts that you use in your database otherwise you might have the "Fit" problem for text that Pat describes.

It is easy to create custom themes with different fonts using my Free TMDThememaker.

Custom themes are stored in the users profile
C:\Users\usersname\AppData\Roaming\Microsoft\Templates\Document Themes
They are XML so you could store a copy of each custome theme in a table in your database then on opening the form that allows the user to change theme copy the XML to the Documents Themes folder then add the file as the attachment using a modified version of the above.

For other reading this post this code is only needed as the Ribbon is not available.

This works in accde.

So nice of you for such an explained post. I will check and update here.
Thanks again.

 

[petertheme]

So nice of you for such an explained post. I will check and update here.
Thanks again.

Glad to be of help.
I am working on some other code to store the theme that was originally used in a database so that it can be replaced if a user picks a theme and then decides is not sensible and want to return to the original after a compact and repair.
I will post that tomorrow or Wednesday.