How to programmatically apply conditional formatting settings to all form controls (304104)

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.

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

   Option Explicit
   					

6. 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
   					

7. Save the module as Module1.
8. Open the Orders form in Design view.
9. Click the Freight text box.
10. On the Format menu, click Conditional Formatting , and then apply the following conditional formats to the Freight text 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
    
    
11. Add a command button named cmdApplyCondFormat to the Orders form.
12. Set the OnClick property of the command button to the following event procedure:

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

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