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.


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.


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.


Sub DisplayUserForm()
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.

  • Change the text inside the box as Input Number.
  • Click on that button box.
  • This will display the UserForm1 window in the spreadsheet.
  • In the textbox, enter your desired number, for example 123456789.
  • Click on OK.

Excel VBA to Format Textbox Number with UserForm

  • This will change the format of the number.

Download Practice Workbook

Related Articles

Get FREE Advanced Excel Exercises with Solutions!


Sabrina Ayon
Sabrina Ayon

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF