Cypris' lookout

MS Access: Changing the Color Scheme programmatically

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.

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

Download 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:

• Setting the BackColor to match the Office 2007 color scheme
• API Get / Set Registry functions from http://www.arcatapet.net/.

6 comments May 3rd, 2008

MS Access: Modal Dialogs with Transparent Backgrounds (redux)

Microsoft Access Team made an interesting post and a follow-up on how to add a transparent layer that cover the screen to focus the attention of the user to a login form or other important popup window.

The trick is to use some WIN 32 API calls to modify the transparency of a standard MS Access form made to cover the screen.

The effect is quite neat and I thought I would try it and make a sample database for others to tinker with it.
My version allows you to chose between covering the whole screen or just the main Access window and it will test if it’s running under a Remote Desktop Terminal and disable the layer in that case.

Update 07MAY2008

Following Rob’s improvements I made another sample database that incorporates his code with a few improvements:

• I added the LightBoxForm.LayerToFullScreen property so users can choose explicitly how they want the layer to be shown.
• I moved the code to hide the layer into a Hide() sub so you can just show/hide the layer using LightboxForm.Show and LightboxForm.Hide.
• I changed the Form’s Resize event code in the LightBoxForm class to avoid flickering: resizing the form within its Resize event actually trigger the Resize event again a second time which causes flickering.
  I simply modified the code to make the form totally transparent (opacity of 0) the first time the event is fired and assign it the expected opacity when the event handler in re-entered.

'----------------------------------------------------------------------------- ' Handle the Layer Form Resize event '----------------------------------------------------------------------------- Private Sub m_objForm_Resize() Static busyResizing As Boolean Dim lngStyle As Long Dim r As RECT ' disable screen updates m_objForm.Painting = False ' When the form opens initially, we make it totally transparent to avoid flickering lngStyle = GetWindowLong(m_objForm.hWnd, GWL_EXSTYLE) SetWindowLong m_objForm.hWnd, GWL_EXSTYLE, lngStyle Or WS_EX_LAYERED SetLayeredWindowAttributes m_objForm.hWnd, 0, 0, LWA_ALPHA ' If the Access window is maximized, then maximize the lightbox form. ' If the Access window is not maximized, then ' position the lightbox form so that it covers the Access window If IsZoomed(hWndAccessApp()) Or m_bLayerToFullScreen Then DoCmd.Maximize Else GetWindowRect Application.hWndAccessApp(), r MoveWindow m_objForm.hWnd, r.x1, r.y1, (r.x2 - r.x1), (r.y2 - r.y1), True End If If busyResizing Then ' Get the current window style, then set transparency lngStyle = GetWindowLong(m_objForm.hWnd, GWL_EXSTYLE) SetWindowLong m_objForm.hWnd, GWL_EXSTYLE, lngStyle Or WS_EX_LAYERED SetLayeredWindowAttributes m_objForm.hWnd, 0, (m_sngOpacity * 255), LWA_ALPHA ' enable screen updates m_objForm.Painting = True ' Back to normal busyResizing = False Else busyResizing = True End If End Sub

Samples

There are now 2 sample databases. Ech zip contains a Microsoft Access 2007 ACCDB file and its conversion to Access 2000¹ and Access 2002-2003 MDB but please note that I have not been been able to test those in older version of Access and that form transparency doesn’t work in Operating Systems older than Windows 2000.

Download TransparentLayer02b.zip (138KB), recommended version
(improved, more flexible version, based on Rob’s updated article).

Download TransparentLayer01b.zip (122KB), original version
(simple code, based Rob’s original article).

Troubleshooting

• If you are getting security warnings: make sure that you open the database from a Trusted Location or you will receive a security prompt. If you don’t know how to do that, check these steps.
• If the layer appears on top of the login form instead of behind: make sure that the top-most form has ist Modal properties set to Yes and the frmLightBox form has its modal property set to No.
  If you improve on it, please let me know and I’ll post it here for all to find.

17 comments May 1st, 2008