Solved Change fonts across all forms

[Kayleigh]

Hi,
I have a database which was recently corrupted and most of the fonts on forms reverted to default (calibri).
Is there a quick way or some code to run which can change the fonts in the Form Header to my desired font?

 

[theDBguy]

Hi. Sorry to hear that. Not sure that there is, unless you named all your headers the same, maybe.

 

[Kayleigh]

Generally the default AutoHeader would be the name.

 

[jdraw]

I found this via Google. Haven't used nor tested it. Don't know how or if it is appropriate.

 

[isladogs]

Don't you have a recent backup you can use?

If you do decide to try the code in the link kindly provided by @jdraw, you may want to add other control types with text such as acCommandButton.
For a complete list of ControlTypes in Access, see
ControlType Property [Access 2003 VBA Language Reference] | Microsoft Docs

You may also want to modify the code to loop through all the reports as well.

 

[Pat Hartman]

I think you can implement the suggestion Colin found but I'd go with reverting to a backup since who knows what else got corrupted. Don't have a backup? Sign up for a cloud backup like Carbonite. You still might be in trouble but at least you'll have something not too old to work with.

Here is one of the techniques I use to ensure that my working databases are backed up frequently. It relies on hard-coding your user name since you don't want the users to be prompted to make backups. Or, you can change the code to use a table instead. And, get even fancier by processing several rows so if i am logged in as PatOffice, or PatHome, it still asks me to back up when I close the db if something has changed.

 

[isladogs]

I've tested the code in the link in post #4 and it works fine as far as it goes.
I've modified the code to:
a) include other controls with text - acCommandButton, acToggleButton, acTabCtl
b) include reports
c) generalise the code so it can be used with different fonts

Place this in a standard module

Public Sub UpdateFonts(strOldFont As String, strNewFont As String)

On Error Resume Next

' Warning: this will globally change the selected font across all forms in the database.
' It is strongly recommended that you backup the database before running.

' Original code at https://ss64.com/access/syntax-fonts.html
' Modified by Colin Riddington 07/07/2021 to add additional controls with text & also include reports

Dim dbs As Object
Dim obj As AccessObject
Dim frm As Form
Dim rpt As Report
Dim ctl As Control

Application.Echo False 'turn off screen updating

Set dbs = Application.CurrentProject
' Loop through the AllForms collection.
For Each obj In dbs.AllForms
    DoCmd.OpenForm obj.Name, acDesign
    Set frm = Forms(obj.Name)

    ' Loop through the controls on each form
    For Each ctl In frm.Controls

        ' Change the Font of text boxes etc
        ' Additional controls containing text added by Colin Riddington
        If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox Or ctl.ControlType = acLabel _
            Or ctl.ControlType = acCommandButton Or ctl.ControlType = acToggleButton Or ctl.ControlType = acTabCtl Then
            If ctl.FontName = strOldFont Then
                ctl.FontName = strNewFont
            End If
        End If
    Next
    
    Set ctl = Nothing

    ' Save the form
    DoCmd.Close acForm, obj.Name, acSaveYes

Next obj

' Loop through the AllReports collection.
For Each obj In dbs.AllReports
    DoCmd.OpenReport obj.Name, acDesign
    Set rpt = Reports(obj.Name)

    ' Loop through the controls on each report
    For Each ctl In rpt.Controls

        ' Change the Font of text boxes etc
        If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox Or ctl.ControlType = acLabel _
            Or ctl.ControlType = acCommandButton Or ctl.ControlType = acToggleButton Or ctl.ControlType = acTabCtl Then
             If ctl.FontName = strOldFont Then
                ctl.FontName = strNewFont
            End If
        End If
    Next
    
    Set ctl = Nothing

    ' Save the report
    DoCmd.Close acReport, obj.Name, acSaveYes

Next obj

Application.Echo True 'turn on screen updating

MsgBox "All controls using " & strOldFont & " font have been updated to " & strNewFont & " in all forms and reports", vbInformation, "Font update completed"

End Sub

To use the code, do something like this

UpdateFonts "Calibri", "Verdana"

 

[Kayleigh]

@isladogs that looks great!
However when I run this updatefonts "Calibri Light (Header)", "Cambria (Header)"
the fonts do not change in the header controls (named Auto_Header0). Is there anything to add in to the code to include?

 

[jdraw]

Did you actually find those values in a list of available fonts?

Open Control Panel (type Control Panel in the
search field and select it from the results). With Control Panel in Icon View
, click the Fonts icon. Windows displays all the installed fonts.

I did/do not see any option to restrict the font change to a header. However, if you can identify the header text, you may be able to change its font with some vba.

 

[isladogs]

Calibri Light (Header) definitely exists though I can only find the default Cambria and Cambria Math
What happens if you specify Cambria instead?

Anyway, the code I supplied worked for all controls on the forms/reports used in testing.
Check whether there is any existing code related to your Auto_Header0 control as that may be resetting the font back to Calibri

 

[Kayleigh]

I think it was the 'Header' it didn't like so tried without and worked like a dream!
Thanks again!

 

[isladogs]

Excellent. Thanks again to @jdraw for finding the original version of the code.
Good luck with the rest of your project

 

[gemma-the-husky]

I think you have to do as described above. I use this sort of method to maintain a consistent style across a database. Maybe insert a standard form background into every form. Things like that.

 

[isladogs]

Just to say, I recently added this code as a web article

Update Fonts

This article contains code that will globally change the selected font across all forms and reports in the database. It is strongly recommended that you backup the database before running the code.

www.isladogs.co.uk

Its also worth mentioning that you can achieve exactly the same effect by changing / editing Access themes