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?
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.
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
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
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.
Code:
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
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.
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.
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
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.
Code:
Public Sub TESTAddNewThemeToMSysResources()
AddNewThemeToMSysResources "C:\Program Files\Microsoft Office\root\Document Themes 16\Slice.thmx"
End Sub
Code:
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.
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")
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.
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.
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.
> By the way what does this (WHERE 1 = 0) where condition do. I failed to understand it.
This makes no current records be in the recordset (1=0 is False). Since records are being added, the recordset doesn't need to have what is already there ~
This may be useful. A little more involved than I first thought.
In a target database It adds a table containing a default theme that you have chosen for that database.
Adding a call to "CheckUsingDefaultTheme()" in the autoexec or startup code will check if the theme has been changed from the default and reset if wanted.
This overcomes the fear of having a theme "Lost" as some people have expressed as a reason for not using themes.
I have been thinking about this but with your users being able to change theme It makes sense to provide a way of returning to the original.
Any comments will be welcome. This provides basic functionality but can be enhanced. It is in accdb format.
