505.369.1650 [email protected]

Many controls used on a Microsoft Access form or report have color properties, like Back Color or Fore Color.  Selecting a color value is easy – just use the color picker in the Property Sheet for the control.  When you choose a custom color, the color value that appears will be a mix of letters and numbers.  For example, #ED1C24 would be the value for a shade of red.

However, if you need to set a color property in VBA, you must use the numeric equivalent of the color value, a Long Integer value.  Passing the color value from the Property Sheet to the function below will return the number that you can use in VBA to set the property.

Running this in the Immediate window:


Would return this:

Public Function ColorToLong(ColorValue As String) As Long
' This function converts a color number to a long integer number.
' For example, if ColorValue = #ED1C24, it would pass back 2366701.
' ColorToLong() Version 1.0.0
' Copyright © 2023 Extra Mile Data, www.extramiledata.com.
' For questions or issues, please contact [email protected].
' Use (at your own risk) and modify freely as long as proper credit is given.
On Error GoTo Err_Handler
    Dim lngR As Long
    Dim lngG As Long
    Dim lngB As Long
    Const strcPrefix As String = "&H"
    ' Red: digits 2 and 3.
    lngR = CLng(strcPrefix & Mid(ColorValue, 2, 2))
    ' Green: digits 4 and 5.
    lngG = CLng(strcPrefix & Mid(ColorValue, 4, 2))
    ' Blue: digits 6 and 7.
    lngB = CLng(strcPrefix & Mid(ColorValue, 6, 2))
    ColorToLong = RGB(lngR, lngG, lngB)
    On Error Resume Next
    Exit Function

    MsgBox Err.Number & " " & Err.Description, vbCritical, "ColorToLong()"
    ColorToLong = 0
    Resume Exit_Proc
End Function