flemingg62
asked on
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
TestMSA.accdb
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
TestMSA.accdb
Just do the same with ForeColor..
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
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
where BackColor and ForeColor are the columns in your data source. Don't hardcode the fore color (white/black) either.
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
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.
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:
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
Full info and more colour functions here: VBA.ModernTheme.
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}
vs.
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
vs.