Amend command button colours via public constants and load event of each form

[chrisjames25]

Hi. Its been a while!!!!!

I have multiple forms in my database. Overtime i may choose to change the color scheme or alternatively just to save me choosing colors for command buttons when building forms i'm looking for a way of setting the command buttons backcolor and forecolor on the load event of a form.

I found the following code on a thread here which got me 90% of way:

Dim ctl as Control
For each ctl in Me.Controls
      If ctl.Tag ="C" Then ctl.Forecolor = vbBlue
Next

To get me the final 10% of way i'd love to know how i could actually now define the wanted color as an rgb statement RGB(117,117,117) as a public constant in a module. This would then allow me to quickly amend all forms if i referenced this constant in each load event of each form.

I defined a constant as follows:

Public Const SC As String = "RGB(203, 203, 203)"

however when i tweaked the above code to this:

Dim ctl As Control
For Each ctl In Me.Controls
      If ctl.Tag = "C" Then ctl.ForeColor = SC
Next

i get an error message. Any help much appreciated. Alternatively if there is a different way to do this im open to ideas.

 

[conception_native_0123]

VBBLUE is a LONG data type when expressed literally in code. it is not technically supposed to be written as "vbBlue". but rather, it would be written like 0xFF0000. per this: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/color-constants

and in this article: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/rgb-function

it says that RGB returns a LONG as well. first of all, try changing your constant to a long type expressed as RGB(203, 203, 203) without the quotes surrounding it. that might work. not sure. if it does, then it follows all what is found in the articles I referenced.

 

[Pat Hartman]

The themes have gotten much better. This will be your least frustrating and manual choice. Pick a theme you like. Be careful though because the font sizes are not all the same and moving from one theme to another might make your controls to small to display the new theme. This is the biggest gotcha. You can modify a theme if you don't like some of the colors.

To standardize on how you want various controls to be formatted create a default form and a default report. Put one of each type of control on the form/report. Then define these as your templates.

 

[chrisjames25]

Hi. THanks for your responses. I'm attmepting to look at the first option and then will look at themes. I have amended my code to be this at moment:

Dim lngRed As Long
lngRed = RGB(255, 0, 0)


Dim ctl As Control
For Each ctl In Me.Controls
      If ctl.Tag = "C" Then ctl.BackColor = lngRed
Next

This works perfect. but the lngred code is within the form rather than public in a module. When i try and make it public it doesnt seem to recognise it. Am i doing something wrong:

Public Const lngred As Long = RGB(255, 0, 0)

I have added the above code to a module. From what i read online i have done it correctly but not working.

 

[chrisjames25]

FOrgot to say, error keeping getting is "constant expression required"

 

[chrisjames25]

I have just read an article stating
Visual Basic doesn't allow public constants in a class
module. THe article was 2001. Is this still the case as this would explain why i cant do what i am trying to achieve.

 

[CJ_London]

A constant has to be a value, not a function

 

[chrisjames25]

Sorry to be stupid but what do you mean has to be a value not a function. Is the RGB thing not deifning a value?

 

[theDBguy]

Hi. THanks for your responses. I'm attmepting to look at the first option and then will look at themes. I have amended my code to be this at moment:

Dim lngRed As Long
lngRed = RGB(255, 0, 0)


Dim ctl As Control
For Each ctl In Me.Controls
      If ctl.Tag = "C" Then ctl.BackColor = lngRed
Next

This works perfect. but the lngred code is within the form rather than public in a module. When i try and make it public it doesnt seem to recognise it. Am i doing something wrong:

Public Const lngred As Long = RGB(255, 0, 0)

I have added the above code to a module. From what i read online i have done it correctly but not working.

Hi. Please pardon me for jumping in, but there's already a built-in constant for the color red: vbRed

So, if all the colors you want to use already have a built-in constant assigned, you can just use those.

Otherwise, you can create your own constants by assigning the literal long integer value for that color. If you don't know what that value is, you can execute the RGB() function in the Immediate Window.

 

[Pat Hartman]

If I understand what CJ said, the only way to set the constant to a function like RGB(), is to use an intermediate variable. Set the variable to the function. Set the constant to the variable. Probably just easier to use Global variables than constants if this is true.

 

[Gasman]

If I understand what CJ said, the only way to set the constant to a function like RGB(), is to use an intermediate variable. Set the variable to the function. Set the constant to the variable. Probably just easier to use Global variables than constants if this is true.

I thought you could not set a constant, except as a true hard coded value?

 

[chrisjames25]

Hi. Please pardon me for jumping in, but there's already a built-in constant for the color red: vbRed

So, if all the colors you want to use already have a built-in constant assigned, you can just use those.

Otherwise, you can create your own constants by assigning the literal long integer value for that color. If you don't know what that value is, you can execute the RGB() function in the Immediate Window.

Thanks for response. Sadly not wanting already defined constants. Please could you tell me how to do constants from the literal long integer.

 

[Pat Hartman]

I don't know. Using the intermediate variable may solve the problem but i did suggest avoiding constants entirely because I don't think this will actually work.

 

[theDBguy]

Thanks for response. Sadly not wanting already defined constants. Please could you tell me how to do constants from the literal long integer.

Okay, let's say you were trying to do this:

 Const MyColor As Long = RGB(111, 111, 111)

We already know that doesn't work. So, all I'm saying is you can do it this way:

Const MyColor As Long = 7303023

If you're asking where I got the value 7303023 from, I executed the following in the Immediate Window:

?RGB(111,111,111)

Hope that helps...

 

[The_Doc_Man]

Sorry to be stupid but what do you mean has to be a value not a function. Is the RGB thing not deifning a value?

Let's look at the two things in question:

Public Const SC As String = "RGB(203, 203, 203)"

The question of where the constant was defined is significant, but for argument's sake let's say this was defined in a General Module, where such a definition would be legal. But take a good look at the data type. Then look at this:

If ctl.Tag = "C" Then ctl.ForeColor = SC

The data type for ctl.ForeColor is a LONG integer, but SC is clearly defined as a string. So... VBA's parser will attempt to find a number in that, but lo and behold, the first thing it finds is three letters. Which cannot therefore be a LONG integer.

In general, an ordinary assignment statement could handle some of these, though I believe the syntax would be a little offbeat. But I have had quite a bit of trouble in defining constants that included functions, even if the result of the function was indeed a constant (because the arguments were also a constant.)

Here is one correct answer, not guaranteed to be exclusively correct. Don't define those things as constants. Define them in general modules as Public VARIABLES of the appropriate data type. In doing so, you could use functions as part of the definition quite easily. Then in your opening form, include something in the Form_Load event routine that calls a "DefineConstants" subroutine that at the beginning of the session defines your "constants" - and at no time do you ever again attempt to define or alter the value of those variables. There ARE advantages in having the constants actually declared as such, but if you truly have special requirements, then perhaps you would do better to use the method I described and just be careful with these constants.

 

[CJ_London]

the thing about class modules is they (or their contents) are not available unless invoked (for example by opening a form). So there is no point in having a public variable within the forms code because public variables need to be available all the time and there is no guarantee the form will be open.

If you want a public constant, put it in a standard module instead. And if you only want to using it in a form, just drop the the public part.

To add to Docs comments a way of having a constant 'variable' would be

Const SC As String = "RGB(203, 203, 203)"

and in your code

If ctl.Tag = "C" Then ctl.ForeColor = eval(SC)

the eval function will try evaluate the SC string and will return (in this case) the number 13355979

 

[The_Doc_Man]

Good one, CJ. I always forget that darned EVAL function. But one last word of advice. IF you are doing hexadecimal colors for Access, the three octets are actually in the order of 0BBGGRR but if you are doing it for Excel's binary colors it is 00RRGGBB. That is, the order is reversed for red and blue. Lucky green gets the middle either way. And of course, Excel has some interesting predefined colors using what they call the color index. So for any casual readers who want to dabble in Access AND Excel coloration, a word to the wise...

 

[chrisjames25]

HI ALl thnaks for the above. Hadn't seen the last few responses before coming up with a solution. THis is what i ended up with. In the class module i have this:

Public Const ConRed As Integer = 117
Public Const ConBlue As Integer = 117
Public Const ConGreen As Integer = 117

And then in the form load event I have the follwoing and will add this to each form i make:

Dim ButtonColor As Long
ButtonColor = RGB(ConRed, ConGreen, ConBlue)

Dim ctl As Control
For Each ctl In Me.Controls
      If ctl.Tag = "C" Then ctl.BackColor = ButtonColor
Next

This seems to do the trick and would allow me to quickly change colour of each button in each form.

Thanks for all your help. I will try and look at the approaches suggested above also as no doubt better than my solution

 

[chrisjames25]

Okay, let's say you were trying to do this:

 Const MyColor As Long = RGB(111, 111, 111)

We already know that doesn't work. So, all I'm saying is you can do it this way:

Const MyColor As Long = 7303023

If you're asking where I got the value 7303023 from, I executed the following in the Immediate Window:

?RGB(111,111,111)

Hope that helps...

Hi DBGuy. This also works a charm. Many thanks

 

[theDBguy]

Hi DBGuy. This also works a charm. Many thanks

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.