asked on 6/15/2021

MS Access, Changing the Colour of Text using VBA

I have a small access database for printing Locker Labels, with the department colour
The colour is controlled by a table Lookup Function. What I want to do, if the department colour is dark have the department name in White not Black. I can bring in the variable for if the department colour is to be Black (FALSE) or White (TRUE) (I can make this text not printable later)
What I need help with is making the Department colour print White if the variable "White Text" is true
DB is attached

Last Comment

6/15/2021

6/15/2021

Just do the same with ForeColor..

flemingg62

6/15/2021

ASKER

Hi Thanks I not very good with VBA and had tried a few option, such as
If [White Text] = TRUE then. [FunctionText].forecolor = #FFFFFF. else. [fieldvalue].forecolor = #000000 vb+color

any idea on how to correctly script it

ASKER CERTIFIED SOLUTION

Gustav Brock

6/15/2021

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

ste5an

6/15/2021

Always use Option Explicit. Add it to every code module. Make it also the default by ticking Set Variable Declaration Required in the VBA IDE under Tools / Options.

Store the color value as compound RGB, then drop the rectangle/frame and use the TextBox. Set its BackStyle to Normal:

Option Compare Database
Option Explicit

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    
  FunctionText.BackColor = [BackColor]
  FunctionText.ForeColor = [ForeColor]
  
End Sub

Open in new window

where BackColor and ForeColor are the columns in your data source. Don't hardcode the fore color (white/black) either.

ste5an

6/15/2021

In VBA, you can't use the hex values.

I can for sure:

Option Compare Database
Option Explicit

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    
  FunctionText.BackColor = &H112233
  FunctionText.ForeColor = &H998877
  
End Sub

Open in new window

The thing you need to now, the hex value in VBA is the hex VBA internal color representation. It is not RGB, its BGR. Thus the order of the color bytes is different then the hex web notation using the hash.

Gustav Brock

6/15/2021

But those are not the CSS values read from the property sheet - so you can't as the questioneer had in mind.

To do that, use a function like this:

' Returns the numeric RGB value from an CSS RGB hex representation.
' Will accept strings with or without a leading octothorpe.
'
' Examples:
'   Color = RGBCompound("#9A690C")
'   ' Color = 813466
'   Color = RGBCompound("9A690C")
'   ' Color = 813466
'
' 2017-03-26. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function RGBCompound( _
    ByVal HexRGB As String) _
    As Long
    
    ' Format of RGB hex strings.
    Const RGBPrefix As String = "#"
    Const Length    As Integer = 6
    ' Format of Hex values.
    Const HexPrefix As String = "&H"
    
    Dim Start       As Integer
    Dim Color       As Long
    
    If Mid(HexRGB, 1, 1) = RGBPrefix Then
        Start = 1
    End If
    If Len(HexRGB) = Start + Length Then
        Color = RGB( _
            HexPrefix & Mid(HexRGB, Start + 1, 2), _
            HexPrefix & Mid(HexRGB, Start + 3, 2), _
            HexPrefix & Mid(HexRGB, Start + 5, 2))
    End If
    
    RGBCompound = Color
    
End Function

Open in new window

Full info and more colour functions here: VBA.ModernTheme.

ste5an

6/15/2021

CAVEAT:

The byte order in web colors using the hash is different the using hex colors in VBA.

It's: #{red}{green}{blue} vs &H{blue}{green}{red}

Option Compare Database
Option Explicit

Public Sub TestColors()

' https://en.wikipedia.org/wiki/Web_colors#HTML_color_names
' CSS 1–2.0, HTML 3.2–4, and VGA color names
' Name      Hex
' Red       #FF0000
' Green     #008000
' Blue      #0000FF
 
  Debug.Print Right("000000" & Hex(vbBlue), 6)
  Debug.Print Right("000000" & Hex(vbGreen), 6)
  Debug.Print Right("000000" & Hex(vbRed), 6)

End Sub

Open in new window

vs.