We help IT Professionals succeed at work.

Use VBA color selector and pass value to control on report

htamraz1
htamraz1 asked
on
I have a report with a header that different people will access using a form where they select their criteria then preview.

Different users need to distinguish their report by assigning a different color to the "Back Color" property of the header control called "CtrlHeader".

I don't want to hard code the "Back Color" values (e.g. #00B7EF), but would like the user to select a color from a palette of some sort (Windows or Access color picker), store the value they select (e.g. #00B7EF) and pass it along to the "Back Color" property of the header control "CtrlHeader".

So,

Step 1 - add control on form to allow user to select color.
Step 2 - open report and pass previously selected color value to back color property of control

Thoughts?

Thank you.
Comment
Watch Question

Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
That should work. I'd suggest you have that form open when the report loads, and use code in the Report's Open event to set the Backcolor of the controls/sections as needed.

You can use API calls to show the color picker:

http://access.mvps.org/access/api/api0060.htm
Distinguished Expert 2017

Commented:
Rather than relying on color, you might want to consider a label that shows the name of the person who created the report or perhaps the selection criteria that was used to create it.  On reports I provide that support selection criteria, I have a box that shows what the user chose.  So it might look like:
ClientStatus = "Active" AND CMName = "Maggie Jones"
htamraz1Director of Technology

Author

Commented:
Scott - I already have access to the Kreft/Lebans solutions and can't get it working. The sample Access 97 db doesn't work for me with Access 2010. Is there an Access color selector function you can suggest with some sample code I can put to use.

Pat - the color coding is an internal requirement for our program officers. Displaying the selection criteria seems logical, but he color option needs to be available.
htamraz1Director of Technology

Author

Commented:
I just landed on this and tested it and I think this will do the trick for me.  Thanks,

https://social.msdn.microsoft.com/Forums/office/en-US/3b95d3bf-1ecb-4c8e-a946-cd87f6194cf9/color-picker-for-access-project?forum=accessdev

Option Compare Database
Option Explicit
 
Declare Sub wlib_AccColorDialog _
  Lib "msaccess.exe" _
    Alias "#53" (ByVal Hwnd As Long, lngRGB As Long)
 
Public Function ChooseWebColor(DefaultWebColor As Variant) As String
  Dim lngColor As Long
  lngColor = CLng("&H" & Right("000000" + _
                  Replace(Nz(DefaultWebColor, ""), "#", ""), 6))
  wlib_AccColorDialog Screen.ActiveForm.Hwnd, lngColor
  ChooseWebColor = "#" & Right("000000" & Hex(lngColor), 6)
End Function
Call this function within a form using following code:
 
Me!txtYourColor = ChooseWebColor(me!txtYourColor)

Open in new window

htamraz1Director of Technology

Author

Commented:
I thought I was done, but I am back with another clarification please...

This function returns a HEX value which I can't seem to pass to the .Backcolor property. Do I need to convert the hex to RGB and use that value instead? Please clarify with an example.

Thanks
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<No Points wanted>
Just some things to consider...
If you let the user pick a color, will that color be standardized for that user?
What if another user selects a color that is "similar" and causes confusion?
What if the user selects the wrong color by accident?
Suppose a black and white printer is all that is available?

JeffCoachman
Distinguished Expert 2017

Commented:
Since there are a limited number of options if you expect to be able to readily identify a person by color, you can never support more than half a dozen users anyway using this method, I would use the VBA names for the colors that you want to allow such as vbRed, vbGreen, etc and you control assigning them to particular users rather than allowing them to pick their own.

I agree with Jeff, color is not a good option.
htamraz1Director of Technology

Author

Commented:
Okay, I appreciate your feedback, but I am not asking specifically what must be done to use HEX for the back color property of an object. Setting aside our disagreements over the use of color, I need an answer.

I see another thread here that was asking a similar question. I am not sure they got a straight answer on this one either.

http://www.experts-exchange.com/Database/MS_Access/Q_28428814.html
htamraz1Director of Technology

Author

Commented:
Adding &H in front of the hex code in VBA seems to be working fine as suggested in this post

http://www.experts-exchange.com/Database/MS_Access/Q_28648349.html

This solved my problem. I have no further questions at this time.
MIS Liason
Most Valuable Expert 2012
Commented:
Try this sample, it works OK for me...

JeffCoachman
Database67.mdb
htamraz1Director of Technology

Author

Commented:
Yup! this works for me too.
The solution I implemented already uses the code I submitted earlier
(see below), but it's good to see this one working as well.

Thank you

Option Compare Database
Option Explicit
 
Declare Sub wlib_AccColorDialog _
  Lib "msaccess.exe" _
    Alias "#53" (ByVal Hwnd As Long, lngRGB As Long)
 
Public Function ChooseWebColor(DefaultWebColor As Variant) As String
  Dim lngColor As Long
  lngColor = CLng("&H" & Right("000000" + _
                  Replace(Nz(DefaultWebColor, ""), "#", ""), 6))
  wlib_AccColorDialog Screen.ActiveForm.Hwnd, lngColor
  ChooseWebColor = "#" & Right("000000" & Hex(lngColor), 6)
End Function
Call this function within a form using following code:
 
Me!txtYourColor = ChooseWebColor(me!txtYourColor)

Open in new window