First Published 11 Oct 2022                 Last Updated 14 Oct 2022


One way of updating the appearance of your apps is to globally change the fonts used
Changing the fonts in all forms and reports manually is time consuming, tedious and prone to errors

Luckily there are easier ways of doing this.
a)   If you use database themes throughout your application, changing the theme can alter both fonts and colours
      For more info, see the Theme My Database article by Peter Cole
b)   Or you can use the simple code below to replace all examples of a selected font with another font.

As an example, the font in all controls of the form & report below have been changed from Calibri to a highly unsuitable font, Viner Hand ITC

Hideous isn't it! Luckily, its also easy to reverse the change!

Click any image to view a larger version

Form - Calibri font	Form - Viner Hand ITC font
Report - Calibri font	Report - Viner Hand ITC font

In either case, you can easily reverse the changes if you don't like the results.
However, it is strongly recommended that you make a backup copy first in the unlikely case of problems.

IMPORTANT:
You should bear in mind that fonts do not all have the same height and width. For example the screenshot below shows an uppercase A in 13 different standard Windows fonts. All are the same point size (72) and font style (Normal)



This means that a control large enough to display text in one font e.g. Calibri may be too small to fully show all that text in another font e.g. Wide Latin.
You should ALWAYS check the output

Many thanks to ex-MVP, Jack Stockton, for correctly pointing out I hadn't originally mentioned this issue in the article.



The code below should be run from a standard module

CODE:

Option Compare Database
Option Explicit

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

On Error Resume Next

' Warning: this will globally replace the selected font across all forms and reports 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

      'turn off screen updating
      Application.Echo False

      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 selected font in text boxes etc
                  ' Additional controls containing text added by Colin Riddington (isladogs)
                  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 _
                        Or ctl.ControlType = acNavigationButton 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 selected font in text boxes etc
                  ' Additional controls containing text added by Colin Riddington (isladogs)
                  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 _
                        Or ctl.ControlType = acNavigationButton 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

      'turn on screen updating
      Application.Echo True

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

End Sub

      Example usage:

      Syntax:     UpdateFonts "OldFont", "NewFont"

      UpdateFonts "Calibri", "Viner Hand ITC"

      UpdateFonts "Viner Hand ITC", "Calibri"

The code only takes a few seconds to run. When it has completed, a message similar to this will be shown:




Click to download:

The module code is available in the attached (zipped) file.
After unzipping, you can then import the modUpdateFonts.bas file directly into the Visual Basic Editor

          modUpdateFonts      (bas file - zipped)



Please use the contact form to let me know whether you found this useful or if you have any questions.



Colin Riddington           Mendip Data Systems                 Last Updated 14 Oct 2022

Return to Code Samples Page

Return to Top