Have you ever had the experience where one event causes you to remember something from years ago? This happened to me today while reading the New Yorker magazine. What does Lucas de Groot, a Dutch typeface designer, have to do with Excel’s VBA language? What is “Fontgate”? Well…read on and I’ll tell you!

Fontgate

According to a article in the New Yorker (July 31, 2017), Mr Groot had designed a font called Calibri approximately 15 years ago. Years later, this font was playing an integral part of a political corruption scandal in Pakistan. Part of the case involved a document that used the Calibri font. The controversy was that one side claimed that the font did not exist at the time that the document was created. This scandal became known as “fontgate”. Fontgate? Good grief….but it really happened.

What’s the Connection to VBA?

About 15 years ago, almost exactly when Mr Groot was working on his Calibri font, I had an interesting Excel request.

Hey Kevin. I want to compare various fonts at the same time to see which one looks the best. Can Excel do this?

Wow! This was an interesting request. The request came from someone who is as obsessed with fonts as I am with Excel. Yes, that is a S E R I O U S font lover! For me, I would just copy/paste the text and change the font manually, but could I make some kind of a tool in Excel?

I remember that it was a Thursday and I stayed up almost the entire night to work on this challenge. Once I got started I couldn’t stop! So there you go….an article from the New Yorker, that I finally got around to reading today, reminded me of my ‘Text Formatter’ Excel creation from the early 2000s.

What Does It Look Like?

This is my Excel userform that allows you to select and compare different fonts. Yeah I know….it might not be pretty but it works! Imagine a proud kid coming home from grade 1 with a picture for mommy & daddy. This is what I felt like so be kind with your comments! Just smile and say “Oh, that’s lovely!”. It was one of my first VBA projects and it was fun.

How Does It Work?

Sheet ‘SampleText’ shows how to open the userform:

1. Select a cell with text
2. Press the ‘Click here to open….’ button

Favorite Format: Set Default (top left orange area)

• Use the drop down to select desired font
• The check boxes toggle bold and italic
• The drop down list changes the font size

Synchronize Preview Option Details (pink area)

• Select options and see how the ‘Preview Options: Modify Details’ section becomes synced!

Preview Options: Modify Details (grey area)

• Use the drop downs to select fonts.
• The check boxes toggle bold and italic
• The drop down lists change the font sizes
• Click orange ‘Fav’ button to load favorite (that’s selected in top left)

Select Preview Option & Update (top right purple area)

• Use the drop down to select a formatted value from grey area
• Press ‘Update Active Cell’ to put this formatted value back into the original cell

Where’s the Beef (vba code) ?

This was all about practicing vba but where is the vba? Go to the ‘Developer’ tab (if you don’t see it a quick google search will show you how to enable it), click on ‘Visual Basic’, and then snoop around to see how it works! Look at ‘UserForm1’. You can see the object and the code behind it (select it and then right click it). Think of additional features and make the modifications!

Please note that you might have to click ‘Enable Macros’ (after opening the file) and also run macro “ListFonts” to create a list of the fonts that you have installed in Excel. Go to Developer/Macros and then select “ListFonts’ to run it.

Why Is This Important?

You can take VBA courses for years and never really learn VBA unless you build something. Think of something that interests you and build it. Back then I built several models using VBA. Some had a practical purpose and some did not but either way I learned a lot.

Where Is The Excel File?

You can download the file here or from my Onedrive (filename is “Kevins Font Formatter.xlsm“)

About Me

My name is Kevin Lehrbass. I live in Markham, Ontario, Canada.  I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. I’m a Data Analyst.

There are so many amazing things that you can do with this powerful software. Check out my videos and my blog posts.

Away from Excel I enjoy learning Spanish, playing Chess, hanging out with Cali and Fenton and watching Excel videos 🙂