Excel VBA to Format Textbox Number with UserForm (With Easy Steps)

Step 1 – Create UserForm in Excel VBA to Format Number

Go to the Developer tab from the ribbon.
Click on Visual Basic under the Code category. Alternatively, press Alt + F11

Excel VBA to Format Textbox Number with UserForm

In the Visual Basic Editor, go to the Insert menu and select UserForm.

Excel VBA to Format Textbox Number with UserForm

This creates a new UserForm window.

Excel VBA to Format Textbox Number with UserForm

To add controls to the UserForm, go to the View menu and select Toolbox.

This displays the Toolbox window with various controls.

Excel VBA to Format Textbox Number with UserForm

Step 2 – Textbox Number Formatting with UserForm

From the Toolbox, drag a Textbox control (third item in the first row) onto the UserForm.

Excel VBA to Format Textbox Number with UserForm

Resize it as needed.

In the Properties window, change the Textbox name to something descriptive, like Numformat.

Double-clicking the Textbox in the UserForm opens the Visual Basic Editor with some default code.
The code that will operate on every modification in the Text Box which is now titled Numformat, is visible in the upper right corner.
To format the number in the Text Box exactly, we will use a different event (Exit instead of Change).

Excel VBA to Format Textbox Number with UserForm

When we click on it, the new code is immediately formatted, and ByVal is defined as our variable.
Write down the full code there to format as a number.

Code:

Private Sub Numformat_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Me.Numformat = Format(Me.Numformat, "#,##0.00")
End Sub

Save the workbook by pressing Ctrl + S.

If we want the text such as 12345678 to be placed into the textbox, it shows as “123,456,78.00“. So, this code indicates that when the user inputs a number, our number will be formatted as seen above (“#,##0,00”).

To include a command button that will allow us to close our User Form, return to the User Form, open the Toolbox, and select the Command Button option. It is the second item in the second row.

Drag the box to set it on the UserForm and change the caption on the box from the Properties window under Caption. And name it OK.

Excel VBA to Format Textbox Number with UserForm

Click on it to apply the code below. This code allows us to close the User Form when we click it.

Code:

Private Sub CommandButton1_Click()
Upload Me
End Sub

Save the project by pressing Ctrl + S.

Step 3 – Present and Exit Userform in Excel VBA to Format Textbox Number

Right-click on the Forms and go to the Insert menu.
Select Module.

Enter this code in the Module window.

Code:

Sub DisplayUserForm()
UserForm1.Show
End Sub

Save the code by pressing Ctrl + S. Remember that you must save the Workbook as a Macro-Enabled Workbook for the codes to be saved.

Return to the spreadsheet and go to the Developer tab.
Click on the Insert drop-down menu under the Controls category.
Choose the first option displayed.

This will open the Macro. If this does not open the Macro, right-click on the box and select the desired Macro. In our example, the only available macro is DisplayUserForm.
Click OK.