Use VBA color selector and pass value to control on report

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.
htamraz1Director of TechnologyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
0
PatHartmanCommented:
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"
0
htamraz1Director of TechnologyAuthor 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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

htamraz1Director of TechnologyAuthor 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

0
htamraz1Director of TechnologyAuthor 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
0
Jeffrey CoachmanMIS LiasonCommented:
<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
0
PatHartmanCommented:
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.
0
htamraz1Director of TechnologyAuthor 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
0
htamraz1Director of TechnologyAuthor 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.
0
Jeffrey CoachmanMIS LiasonCommented:
Try this sample, it works OK for me...

JeffCoachman
Database67.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
htamraz1Director of TechnologyAuthor 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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.