Microsoft Office 2007/2010 comes with 3 colour (color) schemes.
Users can easily change it but when you deploy an Access application under the [Runtime][runtime] your users have no way to set the colour scheme as the application’s options are not available.
(__Article and Code Updated 01DEC2014.__)
Luckily for us, the global colour scheme setting in the registry under the Key:
* Office 2007: `HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Common\Theme`
* Office 2010: `HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Common\Theme`
* Office 2013: `HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Common\UI Theme`
The values being stored under that key are, For Office 2007 and Office 2010:
* 1: Blue
* 2: Silver
* 3: Black
For Office 2013:
* 0: White
* 1: Light Gray
* 2: Dark Gray
With this information, we can easily both read and set the colour scheme.
The only caveat is that I could not find a way to notify Access to reload the setting automatically once it is changed, so users will have to restart the application before the change becomes active.
A small price to pay but if anyone has a better idea, please let me know.
To write the new value to the registry I use a set of WIN 32 APIs that are more flexible than the default ones provided in VBA.
You can download the sample database as it contains all necessary files, including the definition for the Win32 API functions.
Download the ColorSchemeV1.4.zip (46KB) containing the ACCDB database.
The sample also contains some code to restart the database. This is the subject of another post: [Restarting and compacting the database programmatically][1].
###Improvements/uses:###
* Find a way for Access to reload the settings without having to restart the application.
* Use the knowledge about the current scheme to change the other colour settings in the application (or even adapt the form’s theme).
###Updates:###
_v1.4: 01DEC2014_
* Updated code for Office 2013 support.
* Updated Registry API functions for compatibility with 64 bit Office
* Combined all modules into OfficeScheme
* Added new function to return the name of a Theme/current Theme in plain English.
_v1.3: 31MAY2011_
* Updated code for Office 2010 support.
* Autodetect correct registry key for Office 2007/2010
_v1.2: 13FEB2009_
* Updated code for restarting the database.
See exact changes at: http://blog.nkadesign.com/2008/ms-access-restarting-the-database-programmatically/
_v1.1: 09AUG2008_
* Updated code for restarting the database.
See exact changes at: http://blog.nkadesign.com/2008/ms-access-restarting-the-database-programmatically/
* Added license notice to code.
_v1.0: 03MAY2008_
* Original version
###References:###
* Setting the BackColor to match the Office 2007 color scheme
* API Get / Set Registry functions from http://www.arcatapet.net/.
[1]:/2008/ms-access-restarting-the-database-programmatically/
[runtime]:http://www.microsoft.com/downloads/en/details.aspx?familyid=57a350cd-5250-4df6-bfd1-6ced700a6715
Comments
My friend.. Can you provide us with the code to change the color code for office 2010… PD: Is there anyway to make this work for access only? Edwin
@Edwin: I updated the code and sample database to automatically detect the correct registry key for changing the theme. There is no simple way (AFAIK) to make this work for a single Office application as the Theme registry key is used by all of them. The only way I can think of would be to either use a Runtime version separate from the normal Office version the user has installed, or to contain the whole of the runtime installation in a virtualised application environment like ThinApp for instance.
DUDE that is awesome. Something so small yet so awesome thanks
Thank you! It works on a Win XP SP3 system with runtime 2007…
This code is great – but had a problem reading the current scheme on form load under windows 7 x64. The fix is the addition of a single ‘ByVal’ to the RegOpenKeyExA declaration as follows: ByVal dwOptions As Long. Credit is to the answer by DonBr at http://social.msdn.microsoft.com/Forums/en-US/windowscompatibility/thread/073349b1-0f29-41ef-aaab-dbb262d52457/
Is there a way to control the color palette for MS Chart in Access? I can set any color I want in VBA but it always returns the closest match from a preset color palette. I there a way to control the colors in the preset color palette programmatically?
wow, great work dud —> for “MS Access: Changing the Color Scheme programmatically” . I have being thinking about this for quiet a long time.
I have being struggling working around access database called “SUSU_COLLECTOR.accdb” which has a form name “Susu” that contained a subform called “Susu_Tran”. I create a dialog box called “Deposit_Unit” which has 4 controls: “txtBox1”, txtBox2, “Ok” and “Cancel” button. I wanted to use the dialog box’s “txtBox1” and txtBox2 to add new records to the subform “Susu_Tran” on the Main form “Susu”, I had success adding new records to the subform “Susu_Tran” when it is opened in it own windows, but when I try to add new records to the subform on the Main form , am promted a run time error [SUSU_COLLECTOR can’t find the form ‘Deposit_Unit’ referred to in a macro expression or Visual Basic code]. Please any help is greatly appreciated.
@Ray: I would recommend that you post your question on http://stackoverflow.com and include as much information as possible, including screenshots if possible. I’m sure you will have more chance of getting your issue resolved than here. This blog is not really the right venue for this.
Super sweet tool. I have a database that uses the Blue scheme as its default. Using the FileOptions form changes the scheme to Black and will also change it back to Blue. Selecting Silver has no effect. Any thoughts on why the Silver color will not work>
@DaleO : I have updated the code to work with Office 2013. Regarding your issue, Office itself will change to the right scheme, but the values you select for the Background Color property of your forms ends up looking the same in the Silver and Black schemes.
I wonder if this works on machines with Office 2003 installed using the 2010 runtime. I can’t seem to locate the registry entries where the runtime has been installed for this configuration. Anyway for all other installations is an elegant solution, well done.
Comments are closed.