Version 2.22           Last Updated 10 Nov 2021                 Approx 0.7 MB

This is a simple application to demonstrate two different methods for selecting and converting colours between the three different colour formats used by Access:
OLE, RGB & HEX

1.   The colour converter utility can be used to:
      a)   select a colour and view its OLE colour, RGB and HEX values
      b)   convert colour values between these formats




      NOTE: This is a modified version of a utility by Daniel Pineault which is available at his DevHut website

2.   The colour categories utility is intended to assign colours to particular types of event
      For example, I use this in a calendar event application.


      This item also includes the use of the Microsoft colour picker control




To use either of these items in your own applications:

1.   Colour converter
      Copy the form frmColourConverter and module modColourConvert

2.   Colour categories
      Copy the form frmColourCategories, report rptCalendarCategories and module modColorChooser


Update History

v2.20     8 Mar 2020:   Fixed issue with the colour picker dialog in 64-bit Access.
v2.21   26 Dec 2020:   Fixed 2027 date error! Thanks to Dave Thompson for spotting this
v2.22   10 Nov 2021:   Minor changes to tidy up code / add additionsl comments etc


Download

Click to download:     Colour Converter v2.22      Approx 0.7 MB  (zipped)

See also this companion example: Colour Conditions
This shows how to obtain an unlimited number of colour formats without using conditional formatting either in VBA or by using the wizard.


Further reading:

This following quote provides a very clear & detailed explanation of how RGB & hex colour values are related.
It is taken (with permission) from this post by The_Doc_Man at Access World Forums

Part I - what colour constant to use?
In VBA you can present a decimal, octal, or hexadecimal constant. Hex constants are prefixed by &H0 and can be as many digits as needed up to 8 digits (for a long integer) or 16 (if you have a quad integer).

Most people set their displays to 16 million colours which is 24 bits. In this format, you can break up your colours into Red, Green, & Blue parts very easily. If you have a different colour depth for your display, this does not work. Fortunately, these days most displays WILL handle this, and the default for LED and LCD will be 16 million colours.

Here is solid red expressed as 3 bytes: &H0000FF, which is decimal 255 in the low-order byte and nothing in the other bytes. This is solid green: &H00FF00; this is solid blue: &H0FF0000 (and the leading 0 is to prevent something called "sign extension".) If you mix solid red, solid green, and solid blue, you get solid white, which is &H0FFFFFF, six bytes of 255 in each byte (plus the guard against sign extension).

How do you get grey? Grey is just dimmer white. All of these hex values are grey, but different levels of brightness. &H0EEEEEE (still fairly bright), &H0808080 (about 50% grey), &H010101 (the dimmest possible grey that isn't truly black.) By now you have noticed the pattern. If you just express three digit-pairs of the same value in hexadecimal, you get some shade of grey - and unlike the popular novel, you can have 255 shades of grey.

Part II - WHEN do you do this?
Well, the answer is "in some event code" - which leads to the question, WHICH event? And THAT depends on when you want this colour change. I did something similar for my bound forms. I ran a "formatter" subroutine that compared the .Value and .OldValue for any bound field/control pairing. The event that fired my particular colour change was the .LostFocus routine for the given control. Before I left that control, I checked to see if (.Value = .OldValue) and if not, the control's .BackColor got changed from white to (in my case) a shade of pink. The .ForeColor also changed, to a dark shade of maroon. EDIT: This only works this way for BOUND controls. You need a different approach for unbound controls.
If you are going to do this, you need to call your colour formatter subroutine from TWO places - one for the .LostFocus routine and one (for each control) from the FormCurrent routine, so that the controls will be reset to white background or whatever else you were using at the time.
One last colour lesson: How do you do "pink" or "maroon" colours? Answer: Start with knowing that white + red = pink, black + red = maroon. So you take a shade of grey, like &H0B0B0B0, and add red to it to get &H0B0B0FF. Or you take black (&H000000) and add less than the full amount of red. Like &H000020 or &H000040, two dark reds.



Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Do let me know if you find any bugs in the application.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington                       Mendip Data Systems                       Last Updated 10 Nov 2021

Return to Example Databases Page

Return to Top