Standardize Form Color Schemes Globally with VBA

When I release new versions of my software, sometimes I want to change the default color scheme of forms. I like the control VBA gives as compared to the lame built-in Access methods. Thus, I present a simple way to do it.

Realize all forms have one of more sections, and each of these sections has a BackColor property. At a minimum, every form has at least one section, called the Detail section, and, optionally a form footer and a form header section. Continuous forms also have an AlternateBackColor color property for shading alternate rows differently. Here's how I do it:

First, I created a public function in a module called BkGD. It looks like this:

Public Function BkGD(ColNum As Byte, Optional frmName As String) 'colnum is the color scheme and can vary by form On Error GoTo HandleErr

Bkgrd1 = Nz(TempVars!FormTopColor, 15132390) ' the Nz wrapper drops down to a standard color if the tempvar isn't set Bkgrd = Nz(TempVars!FormMiddleColor, 16777215) bkgrd2 = Nz(TempVars!FormBottomColor, 15132390) bkgrd3 = Nz(TempVars!AltRowColor, 15395562)

ExitHere: Exit Function

HandleErr: Select Case Err.Number Case Else Debug.Print "Error " & Err.Number & ": " & Err.Description & " mdl_HandyFunctions End Select End Function

And, at the top of the module, I declare some public variables to set:

Public Bkgrd As Long 'form header section Public Bkgrd1 As Long 'detail section Public bkgrd2 As Long 'form footer section Public bkgrd3 As Long 'alternate row color

I call this function on startup and it reads in some numbers (color numbers) from a table which allows logged in users to each have there own default colors. These values are placed in tempvars, as I'm moving away for globals as much as possible (the break when there is an unhandled crash). I don't think I use the colnum any more, so it's doesn't mean anything now. Since it's not optional, you must put in an integer when you call the function.

Finally, on each form's OnOpen event I put the following statement(s):

Me.FormHeader.BackColor = Bkgrd1 Me.Detail.BackColor = Bkgrd Me.FormFooter.BackColor = bkgrd2 Me.Detail.AlternateBackColor = bkgrd3

Note you must remove the header/footer lines if the form has no header/footer. The AlternateBackColor line can be used all the time since it will be ignored if the form is a continuous form.

Note that older databases can have different names for the sections, such as Detail0 instead of Detail. I'm not sure how or why this happened, but I've seen it in many databases. Just change the names to adjust. The compiler will "remind" you.

If you want to use tempvars, and believe me, they are handy. Refer to this posting by a very good developer, Juan Soto.

#communication #technology #troubleshooting

7 views0 comments