Recently I wanted to change the colour of the controls on a form in Access depending on various conditions using VBA code. This should have been very straightforward but I ended up having to do a bit of extra work to make it happen.
Since Access 2007, the colour properties that you set for controls have been in hexadecimal format. In previous versions of Access these were given as long integers. For instance, a label control with a ‘Fore Color’ property set to black is shown as the hex number ‘#000000’. In prior versions it was shown as 0.
When you want to use this property in VBA it used to be straightforward. You would simply take whatever colour you’d selected in the property sheet and copy it into your code;
lblTest.Forecolor = 0
But supposing we’ve chosen a shade of blue such as ‘#466EA0’ for the forecolor property. When you copy this into your code;
lblTest.Forecolor = “#466EA0”
you’ll get a type mismatch error because you’re trying to set the forecolor property (which is defined as a number) to a text string. Clearly this needs to be converted to a number, so you could try the hexadecimal notation;
lblTest.Forecolor = &H466EA0
but annoyingly this will turn your control’s colour to brown not blue!
The reason is that although ‘#466EA0’ looks like a regular hexadecimal number, it is actually a group of red, green and blue values expressed in hexadecimal format. So the first two characters represent an integer value for the red component, second pair are green and third pair is blue. This is an HTML colour format.
So ‘#466EA0’ is really made up of;
Red = 46 (Hex) = 70 (Decimal)
Green = 6E (Hex) = 110 (Decimal)
Blue = A0 (Hex) = 160 (Decimal)
The color property in VBA code, however, still requires a long integer so you have to convert these HTML colour codes before you can use them in your code.
I wrote a function called ‘HTMLColour’ to do this conversion and I also included the functionality to return the red, green and blue integer values. So now I can put this in my code;
lblTest.Forecolor = HTMLColour(“#466EA0”)
and everything is peachy (or any other colour that you fancy!)
Here’s the function;
Public Function HTMLColour(HTMLCode As String, Optional Red As Variant, _
Optional Green As Variant, Optional Blue As Variant) As Long
On Error GoTo HTMLColour_Error
‘Converts an HTML colour code number to a long interger
‘Also returns the constituent R,G & B components through supplied parameters
Dim intR As Integer, intG As Integer, intB As Integer
Dim strHTML As String
‘Strip # prefix if supplied
If Len(HTMLCode) < 6 Then Exit Function
strHTML = Right(HTMLCode, 6)
‘Extract R, G, B values
intR = CInt(“&H” & Mid(strHTML, 1, 2))
intG = CInt(“&H” & Mid(strHTML, 3, 2))
intB = CInt(“&H” & Mid(strHTML, 5, 2))
‘Return optional parameters
If Not IsMissing(Red) Then Red = intR
If Not IsMissing(Green) Then Green = intG
If Not IsMissing(Blue) Then Blue = intB
‘Convert RGB to Long integer
HTMLColour = RGB(intR, intG, intB)
HTMLColour_Exit:
Exit Function
HTMLColour_Error:
MsgBox Err.Description, vbExclamation, “Function HTMLColour”
Resume HTMLColour_Exit
End Function