Format A User Form Text Box As Currency Or A Percentage

June 23, 2021 - by Bill Jelen

Challenge: You are building a custom user form to calculate a monthly payment, based on loan amount, number of payments, and interest rate. You would like one text box on the form to be formatted as currency and another to be formatted as a percentage. There do not appear to be a properties to format the text boxes.

Solution: You can use the BeforeUpdate code to grab what the person types into a text box and format it properly. If someone types 20, you can have it automatically change to 20% when the user tabs out of the field.

In the VBA editor, right-click your text box and choose View Code. Excel takes you to the code pane and inserts a new procedure called TextBoxName . Change. This is a good guess on Excel’s part, but you are going to use a different event. From the right dropdown at the top of the dialog, choose BeforeUpdate. Excel enters the start of the macro, and you need to fill in the remaining lines.

To format a value as currency with two decimal places, use:

To format a value as a percentage, you must handle the situation where the user already typed in a percentage. You also have to deal with the possibility that someone would enter 20% as 0.2 and someone else might enter 20. Use this code:

The text box derived from this method will look great. The downside is that when you use those values, the percentage value is stored as text. Excel ignores the currency character, but it does not ignore the percent symbol. You need to use the following code to strip out the percent sign and divide the number by 100 before it can be used in a loan calculation:

The resulting user form is shown in Figure 147.

Summary: Although text boxes on user forms do not offer a numeric format property, you can use code to format the values entered in the text box.

Source: formating textboxes in userforms on the MrExcel Message Board.

Title Photo: Artem Beliaikin on Unsplash

This article is an excerpt from Excel Gurus Gone Wild.