How to programmatically apply conditional formatting settings to all form controls

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

↑ Back to the top

Summary

In Microsoft Access 2000, conditional formatting was added to forms and reports to allow users to format a control based on a particular condition. However, in the Access user interface, you cannot automatically propagate the conditional formatting of one control to all other controls on the form. But you can do this by using the FormatConditions collection of the control on which the initial conditional format has been set. This article shows you how to use code to propagate the conditional formatting of one control to all other controls on the form.

↑ Back to the top

More Information

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. For an example of how to propagate the conditional formatting of one control to all other controls on the form, follow these steps.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

If you use Access 2002 or Access 2003, follow these steps:

Start Access.
On the Help menu, point to Sample Databases, and then click Northwind Sample Database.
In the Database window, click the Modules under Objects.
Click New to create a new module.
Add the following line to the Declarations section of the module if it is not already there:
```
Option Explicit
```

Type or paste the following code:

Function AddFormats(ctlSource As Control, frm As Form) As Integer

    Dim ctl As Control
    Dim fcdSource As FormatCondition
    Dim fcdDestination As FormatCondition
    Dim varOperator As Variant
    Dim varType As Variant
    Dim varExpression1 As Variant
    Dim varExpression2 As Variant
    Dim intConditionCount As Integer
    Dim intCount As Integer
    
    intConditionCount = ctlSource.FormatConditions.Count
    
    For Each ctl In frm.Controls
        If ctl.Name = ctlSource.Name Then
            ' This is the source.  Don't apply formatting.
        ElseIf ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
            intCount = 0
            
            ' Bulk remove all current FormatConditions
            ctl.FormatConditions.Delete
            
            Do Until intCount = intConditionCount
                Set fcdSource = ctlSource.FormatConditions.Item(intCount)
                
                varOperator = fcdSource.Operator
                varType = fcdSource.Type
                varExpression1 = fcdSource.Expression1
                varExpression2 = fcdSource.Expression2
                
                ' Add the FormatCondition
                ctl.FormatConditions.Add varType, varOperator, varExpression1, varExpression2
                
                ' Reference the FormatCondition to apply formatting.
                ' Note: The FormatCondition cannot be referenced
                ' in this manner until it exists.
                Set fcdDestination = ctl.FormatConditions.Item(intCount)
                
                With fcdDestination
                    .BackColor = fcdSource.BackColor
                    .FontBold = fcdSource.FontBold
                    .FontItalic = fcdSource.FontItalic
                    .FontUnderline = fcdSource.FontUnderline
                    .ForeColor = fcdSource.ForeColor
                End With
                
                ' Move to the next FormatCondition
                intCount = intCount + 1
            
            Loop
        End If
    Next ctl
    
    ' Cleanup
    AddFormats = intConditionCount
    MsgBox "There were " & AddFormats & " Conditional Format(s) applied to all text and combo boxes except the source."
    Set ctl = Nothing
    Set fcdSource = Nothing
    Set fcdDestination = Nothing
    Set varOperator = Nothing
    Set varType = Nothing
    Set varExpression1 = Nothing
    Set varExpression2 = Nothing
    intConditionCount = 0
    intCount = 0

End Function

Save the module as
Module1.
Open the Orders form in Design view.
Click the Freight text box.
On the Format menu, click Conditional Formatting, and then apply the following conditional formats to the Freighttext box:
- Condition 1 = Field Has Focus, Fill/Back Color = Yellow
- Condition 2 = Field Value Is Equal To 1, Font/Fore Color = Green
- Condition 3 = Expression Is 1+1=2, Underline = True
Add a command button named cmdApplyCondFormat to the Orders form.

Set the OnClick property of the command button to the following event procedure:

Private Sub cmdApplyCondFormat_Click()
    AddFormats Me.Freight, Me
End Sub

Save the Orders form.
Open the Orders form in Form View. Note the conditional formatting of the Freight text box.
Click the command button to apply the Freight text box conditional formatting to all other text and combo boxes on the main form.

If you use Access 2007, follow these steps:

Start Access, and then open sample datebase.
On the Create tab, click Macro in the Other group, and then click Module.
Add the following line to the Declarations section of the module if it is not already there:
```
Option Explicit
```

Type or paste the following code:

Function AddFormats(ctlSource As Control, frm As Form) As Integer

    Dim ctl As Control
    Dim fcdSource As FormatCondition
    Dim fcdDestination As FormatCondition
    Dim varOperator As Variant
    Dim varType As Variant
    Dim varExpression1 As Variant
    Dim varExpression2 As Variant
    Dim intConditionCount As Integer
    Dim intCount As Integer
    
    intConditionCount = ctlSource.FormatConditions.Count
    
    For Each ctl In frm.Controls
        If ctl.Name = ctlSource.Name Then
            ' This is the source.  Don't apply formatting.
        ElseIf ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
            intCount = 0
            
            ' Bulk remove all current FormatConditions
            ctl.FormatConditions.Delete
            
            Do Until intCount = intConditionCount
                Set fcdSource = ctlSource.FormatConditions.Item(intCount)
                
                varOperator = fcdSource.Operator
                varType = fcdSource.Type
                varExpression1 = fcdSource.Expression1
                varExpression2 = fcdSource.Expression2
                
                ' Add the FormatCondition
                ctl.FormatConditions.Add varType, varOperator, varExpression1, varExpression2
                
                ' Reference the FormatCondition to apply formatting.
                ' Note: The FormatCondition cannot be referenced
                ' in this manner until it exists.
                Set fcdDestination = ctl.FormatConditions.Item(intCount)
                
                With fcdDestination
                    .BackColor = fcdSource.BackColor
                    .FontBold = fcdSource.FontBold
                    .FontItalic = fcdSource.FontItalic
                    .FontUnderline = fcdSource.FontUnderline
                    .ForeColor = fcdSource.ForeColor
                End With
                
                ' Move to the next FormatCondition
                intCount = intCount + 1
            
            Loop
        End If
    Next ctl
    
    ' Cleanup
    AddFormats = intConditionCount
    MsgBox "There were " & AddFormats & " Conditional Format(s) applied to all text and combo boxes except the source."
    Set ctl = Nothing
    Set fcdSource = Nothing
    Set fcdDestination = Nothing
    Set varOperator = Nothing
    Set varType = Nothing
    Set varExpression1 = Nothing
    Set varExpression2 = Nothing
    intConditionCount = 0
    intCount = 0

End Function

Save the module as
Module1.
Open the Orders Details form in Design view.
Click the Customer ID dropdown list, click Conditional, and then apply the following conditional formats to the Customer ID dropdown list:
- Condition 1 = Field Has Focus, Fill/Back Color = Yellow
- Condition 2 = Field Value Is Equal To 1, Font/Fore Color = Green
- Condition 3 = Expression Is 1+1=2, Underline = True
Add a command button named cmdApplyCondFormat to the Order Details form.

Set the OnClick property of the command button to the following event procedure:

Private Sub cmdApplyCondFormat_Click()
    AddFormats Me.Customer_ID, Me
End Sub

Save the Order Details form.
Open the Order Details form in Form View. Note the conditional formatting of the Customer ID text box.
Click the command button to apply the Payment Date text box conditional formatting to all other text and combo boxes on the main form.

↑ Back to the top

Keywords: kbfreshness2007, kboffice12yes, kbprogramming, kbvba, kbsweptsoltax, kbinfo, kbbillprodsweep, kbswept, kboffice2003yes, kbhowto, kb

↑ Back to the top

Article Info

Article ID	:	304104
Revision	:	3
Created on	:	4/18/2018
Published on	:	4/19/2018
Exists online	:	False
Views	:	191

Microsoft KB Archive Search

How to programmatically apply conditional formatting settings to all form controls

Summary

More Information