Microsoft gave us conditional formatting, but with an awkward interface and only obscure tools for working with it in code.
Rick Dobson's article
https://vb123.com/listing-conditional-formats/ shows how to list conditional formatting properties you can use when you know which controls have conditional formatting.
This is my more general code, which searches for conditional formatting in all your forms.
To use it, create a new form with a text box (preferably tall and wide) named txtConditionalFormatting. Create an "On click" event for the text box and then replace all the form's code with the code below.
Enjoy!
P Emigh
-----------------------------------------
Option Compare Database
Option Explicit
Private Sub txtConditionalFormatting_Click()
Call sDocumentFormsConditionalFormatting
End Sub
Private Sub sDocumentFormsConditionalFormatting()
On Error GoTo Error_sDocumentFormsConditionalFormatting
' Rick Dobson's article
https://vb123.com/listing-conditional-formats/
' shows how to list conditional formatting properties you can use when you know which controls have conditional formatting.
' This is P Emigh's more general search for conditional formatting, including the formatting and location of the controls.
Dim dbs As Object
Dim frm As Object
Dim ctrl As Control
Dim i As Integer
Dim strHoldFormName
Dim strFormatCondType As String
Dim strFormatCondOp As String
Dim strFormatCondExpr1 As String
Dim strFormatCondExpr2 As String
Dim strIndent
Set dbs = Application.CurrentProject
strIndent = " "
If MsgBox("Start with all forms except this one closed, to avoid problems with subforms. Ready to go?", vbYesNo) = vbYes Then
' Print a heading for the output
Me.txtConditionalFormatting = "Conditional Formatting " & Now
For Each frm In dbs.AllForms
If frm.Name <> Me.Name Then
DoCmd.OpenForm frm.Name, acDesign
For Each ctrl In Forms(frm.Name)
' Conditional formatting is only available to text(109)/list(110)/combo(111) boxes
If ctrl.ControlType >= 109 And ctrl.ControlType <= 111 Then
' If the control has conditional formatting ...
If ctrl.FormatConditions.Count <> 0 Then
'Print the form name, but only once
If strHoldFormName <> frm.Name Then
Me.txtConditionalFormatting = Me.txtConditionalFormatting & vbCrLf & vbCrLf & strIndent & frm.Name
strHoldFormName = frm.Name
End If
' Print the control name and location (top/left properties, in inches)
Me.txtConditionalFormatting = Me.txtConditionalFormatting & vbCrLf & strIndent & strIndent & ctrl.Name & " in " & DecodeSection(ctrl.Section) & " Top: " & FormatNumber(ctrl.Top / 1440, 2) & " in. Left: " & FormatNumber(
ctrl.Left / 1440, 2) & " in."
' Prepare a list of condition properties, but only the pertinent ones
For i = 0 To ctrl.FormatConditions.Count - 1
strFormatCondType = "Condition type = " & DecodeType(ctrl.FormatConditions(i).Type)
strFormatCondOp = ""
If ctrl.FormatConditions(i).Type = 0 Then
strFormatCondOp = " Operator = " & DecodeOp(ctrl.FormatConditions(i).Operator)
End If
strFormatCondExpr1 = ""
If ctrl.FormatConditions(i).Type <> 2 Then
strFormatCondExpr1 = " Expression1 = " & ctrl.FormatConditions(i).Expression1
End If
strFormatCondExpr2 = ""
If ctrl.FormatConditions(i).Type = 0 And ctrl.FormatConditions(i).Operator < 2 Then
strFormatCondExpr2 = " Expression2 = " & ctrl.FormatConditions(i).Expression2
End If
' Print the list of condition properties
Me.txtConditionalFormatting = Me.txtConditionalFormatting & vbCrLf & strIndent & strIndent & strIndent & strFormatCondType & strFormatCondOp & strFormatCondExpr1 & strFormatCondExpr2
' Print the formatting that results from meeting the condition
Me.txtConditionalFormatting = Me.txtConditionalFormatting & vbCrLf & strIndent & strIndent & strIndent & strIndent & " FontBold = " & ctrl.FormatConditions(i).FontBold & " FontItalic = " & ctrl.FormatConditions(i).
FontItalic & " FontUnderline = " & ctrl.FormatConditions(i).FontUnderline
Me.txtConditionalFormatting = Me.txtConditionalFormatting & vbCrLf & strIndent & strIndent & strIndent & strIndent & " BackColor = " & ctrl.FormatConditions(i).BackColor & " ForeColor = " & ctrl.FormatConditions(i).
ForeColor & " Enabled = " & ctrl.FormatConditions(i).Enabled
Next i
End If
End If
Next ctrl
DoCmd.Close acForm, frm.Name
End If
Next frm
End If
Me.txtConditionalFormatting.SetFocus
MsgBox "All done documenting conditional formatting." & vbCrLf & vbCrLf & "Hit Ctl-C to copy and you will be ready to paste it wherever it will be most useful."
Exit_sDocumentFormsConditionalFormatting:
Exit Sub
Error_sDocumentFormsConditionalFormatting:
MsgBox "Error " & Err & ": " & Err.Description
Resume Exit_sDocumentFormsConditionalFormatting
End Sub
Function DecodeSection(SectionProp As Integer) As String
Select Case SectionProp
Case 0
DecodeSection = "acDetail"
Case 1
DecodeSection = "acHeader"
Case 2
DecodeSection = "acFooter"
Case 3
DecodeSection = "acPageHeader"
Case 4
DecodeSection = "acPageFooter"
End Select
End Function
Function DecodeType(TypeProp As Integer) As String
Select Case TypeProp
Case 0
DecodeType = "acFieldValue"
Case 1
DecodeType = "acExpression"
Case 2
DecodeType = "acFieldHasFocus"
End Select
End Function
Function DecodeOp(OpProp As Integer) As String
Select Case OpProp
Case 0
DecodeOp = "acBetween"
Case 1
DecodeOp = "acNotBetween"
Case 2
DecodeOp = "acEqual"
Case 3
DecodeOp = "acNotEqual"
Case 4
DecodeOp = "acGreaterThan"
Case 5
DecodeOp = "acLessThan"
Case 6
DecodeOp = "acGreaterThanOrEqual"
Case 7
DecodeOp = "acLessThanOrEqual"
End Select
End Function
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)