Convert hexadecimal color codes so you can use them in code

Today's guest blogger is Michael Merlin, Lead Software Developer at Electronic Communities.

I recently wasted an hour trying to set a field's Back Color property to the hex code generated by the color picker. I figured it out after some searching, but still nobody online had the exact correct answer. I wrote a function that lets you easily use the hex color in code. The secret is to swap the R and the B, and then convert the hex to long. Further explanation follows in the comments of the function.

Cheers!
Michael

Public Function HexColor(strHex As String) As Long

'converts Hex string to long number, for colors
'the leading # is optional

'example usage
'Me.iSupplier.BackColor = HexColor("FCA951")
'Me.iSupplier.BackColor = HexColor("#FCA951")

'the reason for this function is to programmatically use the
'Hex colors generated by the color picker.
'The trick is, you need to reverse the first and last hex of the
'R G B combination and convert to Long
'so that if the color picker gives you this color #FCA951
'to set this in code, we need to return CLng(&H51A9FC)

Dim strColor As String
Dim strR As String
Dim strG As String
Dim strB As String

'strip the leading # if it exists
If Left(strHex, 1) = "#" Then
strHex = Right(strHex, Len(strHex) - 1)
End If

'reverse the first two and last two hex numbers of the R G B values
strR = Left(strHex, 2)
strG = Mid(strHex, 3, 2)
strB = Right(strHex, 2)
strColor = strB & strG & strR
HexColor = CLng("&H" & strColor)

End Function

• This is a good reference. You can use the vbcolors (vbred, vbblue, etc.) but they are very limited.

• Great stuff! thank you for sharing.

• Nice work. What I have been doing in Access 2010 (and this makes the VBA self-documenting): 1. Select color in Color Picker and click 'More Colors... , 1b. Customize color in need 2. Note RGB values 3. Use RGB function to assign color in VBA Example: to assign #FCA951 as BackColor - [Control].BackColor = RGB(252, 169, 81)

• How do I convert "named" colors? Color names (A2007) are driving me mad...

• Vladimir, there may be an easier way, but this works. 1. Apply the Access Theme color to a control in the Properties dialog, for example - 'Alternate Row'. 2. Run code like this on the form/report load_event: Debug.Print Me.[control].BackColor which returns -2147483610 in the Immediate Window. 3. Assign color as follows: Me.[control].BackColor = -2147483610

• Off-topic: There's a problem with ComboBox background color in A2007. Background color sometimes changes to form's (header, body, footer) background color. The problem appeared in A2007, SP didn't fix the bug.

• Vladimir--do you have a repro? I haven't seen this bug.

• Tony: Thank you for the tip. Quite complicated, though... ;-)