Setting the PageHeader.BackColor property with color codes

[twgonder]

I’m working on my first VBA code today. I knew when I started this I would get into trouble. I’m coming from a different BASIC background.



At the suggestion in another thread, I’m creating a report template to use.
When running a report, I wish to have the ReportHeader and PageFooter change color to that assigned for the application module.

For example, the BackColor for the template will be #DFA7A5, and the standard for the HR (HumanResources) modules will have a default color of #A4D5E2. At least, those are the codes that are used when designing the form and when using the color picker. I may have a HR user that says, “Hey, I hate that color you’re using (Agua Blue 3) can you please change it?” The user decides they like the color #BA1419 (red) and I go into the “Col_Hr” record in the tbl_Au_CntlLoc, to field Pt2 and put in the short text value #BA1419. This will only change the color for people using this FE install of the db (others will still see the default color.

Here is the code I wrote and it failed right where I thought it might.



I assume my variable Temp1 is being treated as a string. It should contain the color code as described above. It fails with Run-time error ‘13’, Type mismatch.
Note: this code works in a template for now, when an actual HR report is created, the “Col_Template” in the VBA code gets changed to “Col_Hr”.

So, I’m guessing the string text we enter into the BackColor property isn’t exactly what VBA expects.

Here are the questions:
1. How can one get into the debugger (immediate or whatever it’s called) to view what the type is for PageHeader.BackColor? How do we view the data type for PageHeader.BackColor?
2. Where are all the data types for the properties documented for easy reference?
3. What conversion will be needed to the string variable of the stored color so that the property can be set?

Thanks

[davegri]

This would have been much easier to provide an example if you would paste actual code with code tags instead of an image of your code, but here goes...
You haven't defined temp1, so just after your commented line at the top of the procedure add

Dim temp1 as string

Then replace the yellow line with

Replace(temp1, "#", "&H"):   ReportHeader.BackColor = CLng(temp1)

In ALL of your code modules the first two lines should be

Option Compare Database
Option Explicit

[June7]

Hex code must follow format of "&HBA1419". Alternatives:

1. Dec equivalent 12194841

2. RGB(Rnum, Gnum, Bnum) intrinsic function - codes shown as a single field in below table but would actually have to be 3 inputs

3. one of the 8 vb color constants, precede color name with vb, like vbBlack

ID	ColorName	ColorCodeDec	ColorCodeHex	ColorCodeRGB
1	Black	0	&H000000	000,000,000
2	Red	255	&HFF0000	255,000,000
3	Green	65280	&H00FF00	000,255,000
4	Yellow	65535	&HFFFF00	255,255,000
5	Blue	16711680	&H0000FF	000,000,255
6	Magenta	16711935	&HFF00FF	255,000,255
7	Cyan	16776960	&H00FFFF	000,255,255
8	White	16777215	&HFFFFFF	255,255,255

[Micron]

IMO, best idea is to design something that meets the requirements such as prints well (when req'd ) fits with the overall scheme (assuming users don't have the ability to play with the colour properties of Windows like we did in the days of 2003 and 2007) and if you get a request for a different colour for some region of a form/report just say "I'll consider it for the next release" and then forget about it. Really, this is about extra work for somebody's idea of what they like for eye candy. It's not important and you shouldn't be wasting time on it unless they are paying customers. It's cool to learn how to manipulate things in code, but there is always going to be zero contribution to efficiency and the bottom line in placating the whims of users for something like this.
Just my 2 cents.

[twgonder]

This would have been much easier to provide an example if you would paste actual code with code tags instead of an image of your code, but here goes...
You haven't defined temp1, so just after your commented line at the top of the procedure add

...

Okay, I'll try to add the code:

Option Compare Database
Option Explicit


Private Sub Report_Load()
    'Change Report header and footer colors to std or custom based on user preference
    Dim temp1 As String
    temp1 = DLookup("Pt2", "tbl_Au_cntlLoc", "ID='Col_Template'")
    If IsNull(temp1) Then
        temp1 = DLookup("Pt1", "tbl_Au_cntlLoc", "ID='Col_Template'")
    End If
    Replace(temp1, "#", "&H"): ReportHeader.BackColor = CLng(temp1)
    
    'Place the copyright message into the page footer
    txtPfNotice = DLookup("Pt1", "tbl_Au_CntlLoc", "ID='AppNotice'")
End Sub

I think I follow what you're doing, but when I try to save it:


I'm guessing the red in the line means there is an error in syntax?
Note: I got the missing ")" fixed, broke it into two lines, and the Replace line remains red. Then I added the temp1 = in front of the replace. It kinda worked, the back color went to blue not red. I also went to the control table and removed all the "#" from the stored string.

Instead of as a string, should they be better stored in the a number type field? If so, which type?

[davegri]

You're missing a close parens at the end of the red line

My bad, the first part should be:

temp1 = Replace(temp1, "#", "&H"):

[June7]

ReportHeader.BackColor = Replace(temp1, "#", "&H")

[twgonder]

I updated my post for some changes,
I'm not sure concatenate works yet, does this work if the "#" is removed from my string?
Still wondering how to store those 3 byte hex codes as a number.

ReportHeader.BackColor = clng("&H"; temp1)

[June7]

Yes, concatenation should work but you are not concatenating. Following works for me.

ReportHeader.BackColor = "&H" & temp1

Did you see post # 3?

[twgonder]

I got this line to work:

  ReportHeader.BackColor = CLng("&h" + temp1)

But now I'm trying to figure out when in the designer and I pick red, I get the code "#BA1419",
but when the code runs the ReportHeader turns a very dark blue.

Does Clng() return something different than the designer does for a conversion in displaying the code?

[twgonder]

Using Clng should error with string input that includes non-numeric characters. I really don't see the need for it. Yes, concatenation should work but you are not concatenating.

ReportHeader.BackColor = "&H" & temp1

Did you see post # 3?

I'll try it your way and report back

[June7]

I get #ED1C24 for a red.

vbRed is FF0000

I get blue with or without CLng using BA1419. Apparently, CLng does not error on this Hex code string.

Ditto with what Micron said. How many users' personal tastes do you want to program for? If you have to distribute a new version of the frontend, are you going to customize for each one?

Coloring reports is problematic. Printers can produce different hues for the same color code. Color can make text totally illegible.

[twgonder]

I get #ED1C24 for a red.

vbRed is FF0000

I get blue with or without CLng using BA1419. Apparently, CLng does not error on this Hex code string.

Ditto with what Micron said. How many users' personal tastes do you want to program for? If you have to distribute a new version of the frontend, are you going to customize for each one?

Coloring reports is problematic. Printers can produce different hues for the same color code. Color can make text totally illegible.

Here is what the designer shows:


I don't understand why the designer shows one thing (red) for the code and using VBA shows another (blue) for the same property code of #BA1419, unless the "#" is doing something weird.

[June7]

I cannot explain issue with BA1419 but vbRed does work.

[davegri]

The problem is transposition. Somehow, due to obscure standards in the far distant past, color management is a mess. For colors to be applied via VBA, you need to swap the Red and Blue hex values.
Meaning #BA1419 will display as red when TYPED into the property in the property sheet, but it will need to be #1914BA to display red when set by VBA in the property sheet. Sounds insane? Try it.

Here's a function that will convert a value such as #BA1419 to a decimal value (with the right color) that can be assigned by VBA to the backcolor. This avoids having to swap the fore and aft yourself.
Want more insanity? You can't TYPE the calculated decimal value from the function into the property sheet - VBA has to do it!!

Function usage: Feed it temp1 as the parameter

ReportHeader.Backcolor = fcnHexToDecimal(temp1)

Here's the function. Add it to a module or you can include it in the report's code module.

Public Function fcnHexToDecimal(ByVal hexColor As String) As String    'Example HexColor = "#00FF1F"
    Dim Red As String
    Dim Green As String
    Dim Blue As String
    hexColor = Replace(hexColor, "#", "")
    Red = Val("&H" & Mid(hexColor, 1, 2))
    Green = Val("&H" & Mid(hexColor, 3, 2))
    Blue = Val("&H" & Mid(hexColor, 5, 2))
    fcnHexColToDecimal = RGB(Red, Green, Blue)
        'The output is a decimal value
End Function