Stopping Fonts from Changing

Jim has a macro that creates an invoice in Excel. The invoice uses various fonts for visual appeal, but now the Aptos Narrow font shows up in the invoices, even though Jim's default font for new workbooks is Arial Narrow. He wonders if there is a way to make sure his desired fonts are used instead of what Microsoft thinks he should use.

There could be several factors at play in Jim's situation, and it will take some sleuthing to figure out what is going on. Jim indicates that he has set the default font for new workbooks to be Arial Narrow. When I hear this, to me it means that Jim has set up a special workbook called book.xltx and stored it in the XLSTART folder. However, Jim may have done it differently, having created a template that defines how he wants his invoices to look. Or, he may have simply created a default worksheet (named sheet.xltx) stored in the XLSTART folder.

You get the idea—it is imperative to know how Jim created his "default font" for new workbooks. This is alluded to in this tip:

https://tips.net/T12618

Read the tip, and you find that Excel allows you to define a default font for new workbooks. However, this font setting is ignored completely if you create the special files in your XLSTART folder. Changing the default font setting in Excel does nothing to change whatever fonts you have defined in the book.xltx or

Now, to make it more complicated, you need to figure out how the macro is creating a new workbook. You can do this by creating a very simple macro:

Sub TestWB()
    Workbooks.Add
End Sub

The simple line will create a new workbook. Now you can analyze what it creates to see if it matches what you may have in the book.xltx template or what you specified in the Excel settings. Once that determination is made, you'll be able to figure out what has to change to get the invoice workbook to look the way you want.

This approach, of course, includes an inherent assumption that the .Add method is how Jim's macro also creates a new workbook. If Jim's macro creates a workbook in a different manner, then that needs to be taken into account—change the above macro to create a new workbook using the command that Jim's macro uses, and then do the analysis to determine the basis of the new workbook.

If all else fails, you can create a "cleanup" macro that can be run after the invoice is created. Here's an example of how this could be handled:

Sub keepArialNarrowFont()
    Dim wsh As Worksheet
    Dim rng As Range
    Dim defFontName As String

    For Each wsh In ThisWorkbook.Worksheets
        'Loop through all cells in the worksheet
        For Each rng In wsh.UsedRange
            'Set the font name for each used cell
            If rng.Font.Name = "Aptos Narrow" Then rng.Font.Name = "Arial Narrow"
        Next rng
    Next wsh
End Sub

The macro steps through all the cells in each worksheet in the workbook and, if the font in that cell is Aptos Narrow, changes the font to Arial Narrow.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13916) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.