Setting Access Colour Codes in VBA

Question

I'm having trouble setting the back ground colour of a textbox in my Access database. I want to change the colour to red when certain conditions are met.

In design view I've set the textbox's back color property to red and it is shown as '#ED1C24'. When I view the form in Form view the control is correctly shown in the red colour I've chosen.

But when I put this value into my VBA code (Text1.Backcolor = "#ED1C24") I get a type mismatch error.

I've tried changing it to a Hex number (Text1.Backcolor = &HED1C24) but then the control turns blue.

Any help would be appreciated. Thanks.

Answer 1

I wrote a blog about this very problem a while ago which should answer your question.

http://www.jht.co.uk/access-colour-color-codes/

Here's the code:

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

Hope this helps.