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
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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Got a workbook cluttered with all sorts of macros? Delete them and you'll make your workbook easier to manage.
Discover MoreWhen you open multiple workbooks, the way in which Excel sizes them is not the best for your needs. This tip looks at a ...
Discover MoreFunctions are a common programming construct. They help you to create easy ways of processing information and returning a ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments