Changing colors

Your VBA code can change the color of objects on forms. Such changes can be handy when you use color-coding to call attention to specific items on a form. For example, if your payment is more than 30 days overdue, you might want to choose the amount due to show up in red (to call attention to the value).

The exact color properties available to you depend on the object for which you're writing code, but some common coloring properties include i BackColor: Defines the background color of a text box, combo box, or form section i BorderColor: Sets the color of the border surrounding a control (providing that border isn't transparent)

i ForeColor: Sets the color of text in controls that show text, such as a text box, combo box, or label

When writing code to change the color of any property listed in the preceding list, use the sytntax = rgbColor where objectName is the name of the object to color, property is one of the properties that accepts a color, and rgbColor is a color defined as a VBA ColorConstant or expression that defines a color as a mixture of red, green, and blue. ColorConstants are just predefined keywords that specify some of the most basic colors, as shown in Table 6-1.

Table 6-1

Basic Color Constants and RGB Values



RGB Equivalent

























The RGB() function allows you to express any of millions of colors. You can use the Color Builder in Access to determine the correct RGB numbers to use to express any color. In forms Design, click the BackColor, BorderColor, or ForeColor property, and then click the Build (ellipsis) button that appears next to the property name. The Color Builder opens, initially showing just the basic colors. Click the Define Custom Colors button to see the whole Color Builder.

To see the RGB numbers for a color, fist click one of the basic colors. Or, click in the larger rainbow-looking area and then choose a brightness to the right of that. The currently selected color will appear in the Color|Solid box, and the RGB numbers for that color appear to the right. Figure 6-6 shows the basic procedure for finding the three numbers necessary to define a color from the Color Builder.

Click a basic color or click a color and brightness.

Figure 6-6:

Use the Color Builder to determine RGB.

Click a basic color or click a color and brightness.

Figure 6-6:

Use the Color Builder to determine RGB.

Background Color Codes Excel

Selected color

RGB of selected color

Selected color

RGB of selected color

Notice in Figure 6-6 how the selected color is expressed as a mixture of Red (238), Green (228), and Blue (45). The way to express that color using the RGB function is simply RGB(238,228,45).

Be aware that backgrounds and borders can also be transparent, meaning they're not visible at all, no matter how you color them. Properties that determine whether an item is transparent or opaque include

1 BackStyle: When set to 0 (zero), the background is transparent. When set to 1, the background is opaque and can therefore show color.

1 BorderStyle: When set to 0 (zero), the background is transparent. When set to 1, the background is opaque and can therefore show color.

As a simple example, suppose your form contains a control named DueDate that contains the date when a payment is due. As you scroll through records in the table, you want DueDate to appear in red whenever the payment is more than 30 days past due. Because you want the control to change while you're scrolling through records on the form, you could attach the code to the form's On Currently event. The code would appear as below in a class module. (The comment above each line of code tells what the line beneath does.)

Private Sub Form_Current()

'Make control background opaque. DueDate.BackStyle = 1 'Make control background color white. DueDate.BackColor = vbWhite 'Make font color red. DueDate.ForeColor = vbRed Else

'Make control background transparent. DueDate.BackStyle = 1 'Make font color black. DueDate.ForeColor = vbBlack End If End Sub

When working with more than the basic colors, many programmers prefer to define colors in advance by storing them in variables. To use this method, you must first declare the variable or constant as a Long (long integer number), and then use the RGB function to assign a value to the variable. For example, the following Dim statements declare a bunch of color names as variables containing Long Integer data. Lines below the Dim statements assign colors to those names:

'Delcare some color names as Long Integer variables.

Dim Beige, Brown, Chartreuse, DarkBlue, DarkGreen As Long

Dim Fuschia, Gold, Gray, HotPink As Long

Dim Lavender, Maroon, Navy, Olive, Orange As Long

Dim Pink, Purple, Salmon, Silver, Teal As Long

'Assign colors to variables as RGB values.

After the color name has been assigned a value, you can use it in your code. For example, the following sets the background color of the form's Detail band to a Salmon color:

Dim Salmon as Long

Forms!Form1.Detail.BackColor = Salmon

For details on creating variables, see Chapter 4.

+2 -1


  • cameron
    How to call color builder in vba?
    8 years ago
  • joonas
    How to give cutomized colors for the font in vba?
    2 years ago

Post a comment