Stopping Fonts from Changing

Written by Allen Wyatt (last updated April 6, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


1

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Using Leaders with Tab Stops

Tab stops allow you to modify the horizontal position at which text is positioned on a line. Word allows you to preface ...

Discover More

Using Message Boxes

If your macro needs to communicate with a user, one simple way to do it is to use a message box. Here's how to use this ...

Discover More

Editing Headers and Footers

Headers and footers are a nice final touch in a document. You can easily edit them by using the methods described in this ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Creating a Function Inventory for a Workbook

Your worksheets are very often made up of formulas and these formulas are made up of functions. If you ever want to ...

Discover More

E-mailing PDF Reports Results in Consistent Crash

It is possible to create macros that send out reports, via e-mail, from within Excel. Frank did this and ran into ...

Discover More

Develop Macros in Their Own Workbook

If you develop macros and edit them quite a bit, you may be running the risk of causing problems with the macros or with ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 5 + 3?

2024-04-06 12:01:35

J. Woolley

Re. the Tip's last macro, I have two nits to pick:
1. Be careful with use of ThisWorkbook, which is a property of the Application object. It refers to the workbook containing the macro, which might be Personal.xlsb, an Excel Add-in (.xlam), or another open workbook. To refer to the active workbook, use ActiveWorkbook instead.
2. I believe there is only one UsedRange in a worksheet; therefore, the macro's second For Each loop is unnecessary.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.