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.