How-to: Bulk Edit Form properties in a database

The script below can be used to loop through all controls on all forms in a database and quickly set various properties.
It also demonstrates the use of the .Tag property to apply a conditional format.

This is useful when converting from .MDB format to .ACCDB which for some unknown reason will reset a bunch of form properties.

Always make a BACKUP of the database before running this, and delete or comment out any properties you don’t want to change.

Sub BulkEdit

Dim frmObj As Access.Form
Dim frm As Object
Dim ctl As Control

'Debug.Print " Loop through all controls on all forms"
For Each frm In CurrentProject.AllForms

    ' Only open forms with a name that contains 'frm' e.g. frmDemo
    If InStr(frm.Name, "frm") And Not InStr(frm.Name, "switchboard") Then
        'Debug.Print frm.Name
        DoCmd.OpenForm frm.Name, acDesign
        
        ' Set a new variable to the now open form object
        Set frmObj = Forms(frm.Name)
        
        ' Loop through all the controls and set desired properties
        For Each ctl In frmObj.Controls
           'Debug.Print ctl.Name
           Select Case ctl.ControlType
                Case acBoundObjectFrame
                Case acCheckBox
                Case acComboBox
                    ctl.BackColor = 16119285
                    ctl.BorderColor = 7633277
                    ctl.FontName = "Microsoft Sans Serif"
                    ctl.FontSize = 8
                Case acCommandButton
                    ctl.UseTheme = True
                    ctl.BackColor = 12566463
                    ctl.BorderColor = 7633277
                    ctl.HoverColor = 10855845
                    ctl.PressedColor = 12566463
                    ctl.HoverForeColor = 0
                    ctl.PressedForeColor = 0
                    ctl.FontName = "Microsoft Sans Serif"
                    ctl.FontSize = 8
                ' Case acCustomControl
                    ' ActiveX, don’t use
                Case acImage
                Case acLabel
                    '  ctl.ForeColor = 0
                    '  ctl.BackColor
                Case acLine
                Case acListBox, acComboBox
                    'ctl.ForeColor
                    'ctl.BackColor
                Case acObjectFrame
                Case acOptionButton
                Case acOptionGroup
                Case acPage
                Case acPageBreak
                Case acRectangle
                    'ctl.BackColor
                Case acSubform
                Case acTabCtl
                    ctl.UseTheme = True
                    ctl.BackColor = 14211288
                    ctl.BorderColor = 7633277
                    ctl.HoverColor = 12349952
                    ctl.PressedColor = 11450043
                    ctl.HoverForeColor = 0
                    ctl.PressedForeColor = 0
                    'Debug.Print "Tabctl BorderColor: " & ctl.BorderColor
                Case acTextBox
                    'ctl.ForeColor
                    'ctl.BackColor
                    ctl.BorderColor = 7633277
                    If ctl.Tag = "Header" Then
                        ctl.FontName = "Georgia"
                        ctl.FontSize = 12
                    Else
                        ctl.FontName = "Microsoft Sans Serif"
                        ctl.FontSize = 8
                    End If
                Case acToggleButton
        End Select
        Next
        
        ' Save the form
        DoCmd.Close acForm, frm.Name, acSaveYes
        DoEvents
    End If
Next

End Sub

“You will never stub your toe standing still. The faster you go, the more chance there is of stubbing your toe, but the more chance you have of getting somewhere” ~ Charles F. Kettering

Related:

Colors - Access color codes.
Functions - Access functions in SQL or VBA.