MS Access: Changing the Color Scheme programmatically

Saturday, May 3, 2008

Microsoft Access 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 your users have no way to set the colour scheme as the application’s options are not available.
(Article and Code Updated 31MAY2011.)

Luckily for us, Office 2007 stores the global colour scheme setting in the registry under:
HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Common\Theme
and Office 2010 in:
HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Common\Theme

The values being stored under that key are:

  1. Blue scheme
  2. Silver scheme
  3. Black scheme

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.

Office 2007 Colour Schemes

You can download the sample database as it contains all necessary files, including the definition for the Win32 API functions.

DownloadDownload the ColorSchemeV1.3.zip (31KB) 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.

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:

  • 31MAY2011: added support for Office 2010.

References:

Entry Filed under  :  Database,MSAccess,Programming

11 Comments Add your own

  • 1. Access Team Blog&hellip  |  May 6th, 2008 at 8:47 am

    Follow up to Transparency Forms…

    Renaud posted some comments to the previous post about transparency forms and made some really cool enhancements…

  • 2. Windows Themes and Access&hellip  |  April 16th, 2010 at 7:32 pm

    [...] this link http://blog.nkadesign.com/2008/ms-ac…grammatically/ Should help get you started. Dan Access Development [...]

  • 3. Edwin Blancovitch  |  May 31st, 2011 at 4:31 am

    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

  • 4. Renaud  |  May 31st, 2011 at 9:57 am

    @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.

  • 5. Stephen Poynter  |  August 25th, 2011 at 4:56 pm

    DUDE that is awesome. Something so small yet so awesome thanks

  • 6. Klaus  |  November 25th, 2011 at 6:11 pm

    Thank you!

    It works on a Win XP SP3 system with runtime 2007…

  • 7. Simeon Bartley  |  August 31st, 2012 at 12:19 pm

    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/

  • 8. JC  |  April 16th, 2013 at 10:23 pm

    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?

  • 9. Ray  |  August 13th, 2013 at 6:11 am

    wow, great work dud —> for “MS Access: Changing the Color Scheme programmatically” . I have being thinking about this for quiet a long time.

  • 10. Ray  |  August 13th, 2013 at 7:05 am

    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.

  • 11. Renaud Bompuis  |  August 13th, 2013 at 4:00 pm

    @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.

Leave a Comment

(Will not be shown)
Notify me of follow-up comments via e-mail

Some HTML allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Trackback this post  |  Subscribe to the comments via RSS Feed


about

Renaud This is a simple technical weblog where I dump thoughts and experiences from my computer-related world.
It is mostly focused on software development but I also have wider interests and dabble in architecture, business and system administration.
More About me…

My StackOverflow Profile
My (sporadically active) StackOVerflow account

Most Recent Posts

Categories

Links