In Microsoft Excel, a UserForm is a custom-created dialog box that enables user information entering more manageable and user-friendly. The UserForm Textbox in Excel VBA is one of the UserForm controls in the TextBox. On the UserForm, you may choose and move a TextBox. In this article, we will demonstrate the step-by-step guidelines of the Excel VBA UserForm Textbox number format.
Excel VBA to Format Textbox Number with UserForm (With Easy Steps)
A form has controls, including boxes or dropdown lists, that can help users who use the spreadsheet input or amend data more easily. In Excel, we may make a form by attaching text elements to a worksheet, such as buttons, combo boxes, list boxes, etc. The data type UserForm is an Object. Variables can be defined as type UserForm before being assigned to an instance of a UserForm type created at design time.
The UserForm Textbox is used to show text or to modify the text that is already displayed in the TextBox. Now, let’s follow the steps below to format the textbox to numbers with UserForm.
Step 1: Create UserForm in Excel VBA to Formate Number
The first thing we have to do is design a UserForm. For this, we need to follow the subprocedure down.
- Firstly, we need to launch Visual Basic. To do so, go to the Developer tab from the ribbon.
- Secondly, click on Visual Basic under the Code category.
- Instead of doing this, you can open Visual Basic by pressing Alt + 11.
- Once you open the Visual Basic window. Click on the Insert menu and select UserForm from the drop-down menu.
- Now, by doing this, you can see the UserForm is created.
- Next, we have to add the Toolbox to the UserForm in terms of controlling it. So, go to the View tab and click on Toolbox from the drop-down option.
- This will appear in the Toolbox window.
Step 2: Textbox Number Formatting with UserForm
Now, the main procedure is to use the UserForm format as a number in the Textbox. Let’s see the sub-steps below.
- There seem to be a variety of handy controls in the Toolbox. As we format numbers in the Textbox, we will employ a Text Box. It is the third item in the first row.
- Further, drag the box as per the requirement.
- Furthermore, we will rename our Textbox to Numformat in the Properties window.
- After that, if you double click on the Textbox in the UserForm, the Visual Basic Editor will show up with some codes in it.
- The automated function was established, as you can see. The code that will operate on every modification in the Text Box which is now titled Numformat, is visible in the upper right corner.
- Therefore, in order to format the number in the Text Box exactly, we will use a different approach, not Change, but Exit.
- You will note that we have a lot of alternatives to pick from in the dropdown menu.
- And, you can see that when we click on it, the new code is immediately formatted, and ByVal is defined as our variable.
- Now, 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- After that, 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”).
- Suppose we also want to include a command button that will allow us to close our User Form. We’ll return to our User Form, open the Toolbox, and select the Command Button option. To do this, click on CommandButton. 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.
- Finally, simply put OK on it and then click on it to apply the code below. This code just allows us to close the User Form when we click it. This is how the User Form looks. Despite the fact that we defined the User Form and the code associated with it.
Code:
Private Sub CommandButton1_Click()
Upload Me
End Sub- By the same token as before, save the project by pressing Ctrl + S.
Step 3: Present and Exit Userform in Excel VBA to Format Textbox Number
Despite the fact that we established our User Form and the code associated with it, people cannot view it in our workbook. To make this UserForm accessible, we must add some code. The final step is to display and close the user form. And for this, we have to follow the sub-steps below.
- Firstly, right-click on the Forms and then, go to the Insert menu.
- After that, click on Module.
- Now, write the code on the Module window.
Code:
Sub DisplayUserForm()
UserForm1.Show
End Sub- All we have to understand for this procedure to function is the name of our User Form, in this instance UserForm1. Then save the code by pressing Ctrl + S. Remember that you must save the Workbook as a Macro-Enabled Workbook in order for the codes to be saved.
- Then, returning to the spreadsheet, we will navigate to the Developer tab.
- After that, click on the Insert drop-down menu under the Controls category.
- Next, choose the first option displayed.
- This will open the Macro if not, then, right-click on the box and select the Macro in our example, the only available macro is DisplayUserForm.
- Click OK.
- We change the text inside the box as Input Number.
- If we click on that button box, this will display the UserForm1 window in our spreadsheet.
- Here, in the textbox, we can put our desired number for example 123456789.
- Click on the OK button.
- This will change the format of our number as we wanted.
As we can see that we correctly formate the number as per our requirement.
Download Practice Workbook
You can download the workbook and practice with them.
Conclusion
The above steps will assist you to use a UserForm textbox to number format in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section.
Related Articles
- Excel VBA: Show Userform in Full Screen
- Excel VBA: UserForm Image from Worksheet
- How to Use Excel UserForm as Date Picker
- Excel VBA: Print UserForm to Fit on a Page
- How to Use VBA to Get Value from Userform Textbox in Excel
- How to Create a UserForm: an Overview
- How to Create Toggle Button on Excel VBA UserForm
