Adding Additional Conditional Formatting in Access with VBA
Microsoft Access 2000 (and later) lets you conditionally format the data in combo box and text box controls based on certain conditions through the user interface or through programming. However, each control is limited to a maximum of three conditional formats at any one time. This blog demonstrates creating conditional formatting through VBA programming.
As just stated, there are two ways to create and apply conditional formats. You can select the control and click Conditional Formatting on the Format menu. You can also use VBA to create instances of FormatCondition objects with conditional formatting and add these to the FormatConditions collection.
Each conditionally-formatted control has at least two formats; a default format and a special format when a specific condition is True. A control can have up to three special format conditions. The three-condition limit can be overcome by using VBA code to program these conditions to have different values under different circumstances. By creatively using code, you can manage conditional formats so that to the user it appears that there are more than the three conditions allowed. You might also want to use conditional formatting in multiple forms or applications. Creating the conditions in code makes it much easier to apply identical formats to different forms by copying and pasting the code.
Every combo box and text box on a form has a FormatConditions collection containing FormatCondition objects. The FormatConditions collection has an Add method and a Delete method. The FormatCondition object has the Modify method. FormatCondition objects don't have a name property so they are commonly referred to by their index numbers. In addition, FormatCondition objects have property settings such as the Type property that lets you apply a condition based on a field value or expression.
Assume that you have a form that formats the data or background color in a "Result" text box depending on the value of a target number and a comparison option. In this form, the user types a number in the Target text box and clicks an option such as the following:
Option 1:
Italicize, Underline if Result < Target number
Bold, Underline if Result = Target number
Bold, Italicize if Result > Target number
Option 2:
Back Color=Red, Fore Color=Black if Result < Target number
Back Color=Black, Fore Color=Red if Result = Target number
Back Color=Yellow, Fore Color=Red if Result > Target number
Option 3:
Enabled = False if Result < Target number
Enabled = True if Result = Target number
Enabled = False if Result > Target number
Option 4:
Back Color = Yellow, Fore Color = Black if today is Saturday
Back Color = Black, Fore Color = Red if today is a weekday
Back Color = Red, Fore Color = Black if today is Sunday
The user then types a number in the Result box and clicks a Change button. For the first three options, the value in the Result box is compared to the value in the Target box, and depending on whether the two values are less than, equal to, or greater than each other, formats the Result box. For Option 4, when the Change button is clicked, the current day of the week is compared to the strings Sat and Sun to determine how the Result box is formatted.
Private Sub cmdChange_Click()
' This subroutine demonstrates the use of FormatCondition objects
' to add formatting to a text box. It also illustrates ways you can
' extend the functionality of the three conditional-format limit
' allowed in the FormatConditions collection.
'
'
Dim objFrc As FormatCondition
Dim lngRed As Long
Dim lngWhite As Long
Dim lngBlack As Long
Dim lngYellow As Long
' Set up background and foreground colors.
lngRed = RGB(255, 0, 0)
lngWhite = RGB(255, 255, 255)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
' Remove any existing format conditions.
Me![txtResult].FormatConditions.Delete
' Create three format objects and add them to the FormatConditions
' collection.
Set objFrc = Me![txtResult].FormatConditions.Add(acFieldValue, _
acLessThan, Me![txtTarget].Value)
Set objFrc = Me![txtResult].FormatConditions.Add(acFieldValue, _
acEqual, Me![txtTarget].Value)
Set objFrc = Me![txtResult].FormatConditions.Add(acFieldValue, _
acGreaterThan, Me![txtTarget].Value)
' Depending on the user's option selection, format the txtResult
' box.
Select Case optgrpChoice.Value
Case 1
' Refer to each format condition by its index.
With Me![txtResult].FormatConditions(0)
.FontBold = False
.FontItalic = True
.FontUnderline = True
End With
With Me![txtResult].FormatConditions(1)
.FontBold = True
.FontItalic = False
.FontUnderline = True
End With
With Me![txtResult].FormatConditions(2)
.FontBold = True
.FontItalic = True
.FontUnderline = False
End With
Case 2
With Me![txtResult].FormatConditions(0)
.BackColor = lngRed
.FontBold = True
.ForeColor = lngBlack
End With
With Me![txtResult].FormatConditions(1)
.BackColor = lngBlack
.FontBold = True
.ForeColor = lngRed
End With
With Me![txtResult].FormatConditions(2)
.BackColor = lngYellow
.FontBold = True
.ForeColor = lngRed
End With
Case 3
Me![txtResult].FormatConditions(0).Enabled = False
Me![txtResult].FormatConditions(1).Enabled = True
Me![txtResult].FormatConditions(2).Enabled = False
Case 4
' Here, we redefine the formatting based on an expression
' comparing today to string values.
Me![txtResult].FormatConditions.Delete
Set objFrc = Me![txtResult].FormatConditions. _
Add(acExpression, , (Format(Now(), "ddd") = "Sat"))
Set objFrc = Me![txtResult].FormatConditions. _
Add(acExpression, , (Format(Now(), "ddd") <> "Sat") And _
(Format(Now(), "ddd") <> "Sun"))
Set objFrc = Me![txtResult].FormatConditions. _
Add(acExpression, , (Format(Now(), "ddd") = "Sun"))
With Me![txtResult].FormatConditions(0)
.BackColor = lngYellow
.FontBold = True
.ForeColor = lngBlack
End With
With Me![txtResult].FormatConditions(1)
.BackColor = lngBlack
.FontBold = True
.ForeColor = lngRed
End With
With Me![txtResult].FormatConditions(2)
.BackColor = lngRed
.FontBold = True
.ForeColor = lngBlack
End With
End Select
End Sub
Let's examine the code in a little more detail.
First, we declare a variable objFrc as a FormatCondition object. These objects will contain our format conditions. Next, we set up the colors that we will use for formatting the Results box.
' Set up background and foreground colors.
lngRed = RGB(255, 0, 0)
lngWhite = RGB(255, 255, 255)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
Then we remove any existing FormatCondition objects by using the Delete method of the FormatConditions collection.
Me![txtResult].FormatConditions.Delete
This is necessary to make sure that we sufficient room for the three objects we will create. I should also note that individual FormatCondition objects can be deleted by referencing the object with an index number such as:
Me![txtResult].FormatConditions(1).Delete
Next, we define the specific format conditions and add them to the FormatConditions collection by using the Add method.
Set objFrc = Me![txtResult].FormatConditions.Add(acFieldValue, _
acLessThan, Me![txtTarget].Value)
Set objFrc = Me![txtResult].FormatConditions.Add(acFieldValue, _
acEqual, Me![txtTarget].Value)
Set objFrc = Me![txtResult].FormatConditions.Add(acFieldValue, _
acGreaterThan, Me![txtTarget].Value)
The expression and the control determine when to apply the format and what control to apply it. For example, the first statement applies the format to the txtResult control if its field value is less than the field value of the txtTarget control, and so on for the other conditions.
Next, we use a series of Select Case statements determine which format to apply when a condition is triggered. It does this by evaluating the output of the choose an option area (the optgrpChoice control). For example, if the user selects option 1 and the value in the Result box is greater than the value in the Target box, the following section of code is executed when the Change button is clicked:
With Me![txtResult].FormatConditions(2)
.FontBold = True
.FontItalic = True
.FontUnderline = False
End With
Here, the third format condition (using a zero-based index) applies a bold and italic format to the Result box. Different combinations of formatting are illustrated with the remaining Select Case statements.
Option 4 is a special case in that when the user clicks it, we remove the existing FormatCondition objects from the collection and dynamically create new ones.
Me![txtResult].FormatConditions.Delete
Set objFrc = Me![txtResult].FormatConditions.Add(acExpression, _
, (Format(Now(), "ddd") = "Sat"))
Set objFrc = Me![txtResult].FormatConditions.Add(acExpression, _
, (Format(Now(), "ddd") <> "Sat") And _
(Format(Now(), "ddd") <> "Sun"))
Set objFrc = Me![txtResult].FormatConditions.Add(acExpression, _
, (Format(Now(), "ddd") = "Sun"))
In these conditional definitions, we are using an expression to derive the day of the week from today's date in a three-character format and comparing it to the strings Sat and Sun to determine if the day falls on a weekday or weekend. The Access Format function is used to change today's date into the three-character string. We indirectly determine whether today's date is a weekday by testing to see that it is not equal to the strings Sat or Sun. Once we have set up our new format conditions, we then define the new formatting that will be applied when a particular format condition is met.
Hopefully this discussion can provide a basis for adding additional conditional formats to you own code.