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.

Possible duplicate of [How do I get the corresponding Hex value of an RGB color in Excel/VBA?](http://stackoverflow.com/questions/6003324/how-do-i-get-the-corresponding-hex-value-of-an-rgb-color-in-excel-vba) — Andre, Nov 14 '16 at 12:30

score 5 · Answer 1 · edited Mar 29 '20 at 10:11

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.

A link to a potential solution is always welcome, but please [add context around the link](http://meta.stackoverflow.com/a/8259/169503) so your fellow users will have some idea what it is and why it’s there. Always quote the most relevant part of an important link, in case the target site is unreachable or goes permanently offline. Take into account that being _barely more than a link to an external site_ is a possible reason as to [Why and how are some answers deleted?](http://stackoverflow.com/help/deleted-answers). — elixenide, Nov 22 '16 at 02:06

score 1 · Accepted Answer · answered Nov 14 '16 at 12:30

The color code format in VBA is RGB or Long, and not HEX

In your case the easiest way is to call a function that will convert from HEX to Long:

Public Function Color_Hex_To_Long(strColor As String) As Long
    Dim iRed As Integer
    Dim iGreen As Integer
    Dim iBlue As Integer

    strColor = Replace(strColor, "#", "")
    strColor = Right("000000" & strColor, 6)
    iBlue = Val("&H" & Mid(strColor, 1, 2))
    iGreen = Val("&H" & Mid(strColor, 3, 2))
    iRed = Val("&H" & Mid(strColor, 5, 2))

    Color_Hex_To_Long = RGB(iRed, iGreen, iBlue)
End Function

Use it like this :

Text1.BackColor = Color_Hex_To_Long("#ED1C24")

this did *not* work for me (Access 365) - the integer calculated from the Hex values as shown in Access' form properties differs from the ones I get by reading the color number by code after setting it manually => wrong colors in result. The solution of jhTuppeny below did the job. — martin.lindenlauf, Mar 28 '20 at 17:05

score 1 · Answer 3 · answered Mar 26 '20 at 01:55

1

Simply use OnCurrent properties to set your font properties or other properties. Instead of entering the Hex color codes, easier to use MS Access proprietary codes that are entirely in numbers. Do the easy way. Cheers! Mikey

answered Mar 26 '20 at 01:55

Shananarocks

11
2

score 0 · Answer 4 · answered May 25 '18 at 06:44

For MS_ACCESS 2016 the long value seems to be just the .backcolor value, converting the HEX using the functions above won't work.

I'd just create a text box and a label, colour the label as you wish in design view and set the textbox value to txtBlue = lblBlue.backcolour in VBA.

I'm not sure if this is the case in other versions of excel but it seems to be the case in Office 2016.

Setting Access Colour Codes in VBA

4 Answers4