Updated June 9, 2023
What is VBA TextBox in Excel?
VBA TextBox is one of the many controls from UserForm controls in Excel. VBA TextBox can come handy when you want to have input from user like their name, age, address, salary, etc. Using TextBox control, you can allow users to input this kind of information and save many of your time as well as typo errors. VBA TextBox can consist of Static or Dynamic Data.
UserForm itself is a very useful tool which has different control options like TextBox, MsgBox, and InputBox, etc. which provide a more practical way to get data from user so that you can functionally process that data and find out some useful insights out of it.
In this article, you will be getting hands-on experience on Excel VBA TextBox.
Examples of Excel VBA TextBox
Below are the different examples of VBA TextBox in Excel.
Example #1 – How to Insert TextBox in VBA?
Below are the steps to insert TextBox in Excel VBA.
Step 1: Open an excel file and hit Alt + F11 to navigate to VBA pane.
Step 2: As we already have discussed, TextBox is an option available in UserForm. So we need to insert UserForm in VBE. Go to Insert Menu > Click UserForm.
Step 3: As soon as you insert a UserForm in VBA you can see a Toolbox option associated with it and have different options associated with UserForm.
Step 4: In this toolbox select TextBox.
Step 5: Drag this TextBox on the user form.
Step 6: Hit F4 to see the properties of UserForm (Alternatively you can do this by right clicking on UserForm inserted). These properties allow you to make some necessary changes like changing the name of UserForm to be able to access it easily.
Some other basic changes you can do with properties pane of UserForm are like changing the Font, Changing the size, etc.
Step 7: Input the text as “Welcome to VBA TextBox!” in TextBox under UserForm.
This is how you can create a VBA TextBox.
Example #2 – Setting Value to VBA TextBox
Let’s see how we can set the values for TextBox. It is sometimes mandatory to set the values otherwise, the user will input anything of their interest.
Step 1: Insert a New UserForm in VBE. Go to Insert menu > click on UserForm.
Step 2: From UserForm Tools, select TextBox and drag it to UserForm.
Step 3: Insert a CommandButton under UserForm in the same way you added TextBox.
Step 4: Change the caption of the command button to “Submit” under properties. You can navigate to properties of command button by Right Click on Command Button > Click Properties.
Step 5: Double click on the submit button or Right click on it and select View Code.
Step 6: In the code, insert a line of new code within sub-procedure as below.
Code:
Private Sub CommandButton1_Click() TextBox1.Value = "My Name is Lalit!" End Sub
This highlighted code allows you to assign the value at right enclosed in double quotes to the TextBox. You can also use TextBox.Text property instead of TextBox.Values to set the value for TextBox.
Step 7: Hit F5 or Run button under VBE to run this code, you will see a UserForm with empty TextBox. As soon as you hit the submit button, the text assigned to TextBox will appear there in it.
Example #3 – Restrict TextBox Input to Accept only Numeric Values
Follow the below steps:
Step 1: Insert a new UserForm in your VBA and add TextBox in it.
Step 2: Change the name of TextBox under Properties section to “My_Age”.
Step 3: Double click on TextBox or right click on it and select View Code.
You’ll be able to see the initial sub-procedure as below:
Code:
Private Sub My_Age_Change() End Sub
Step 4: Now, try to add an If the condition which starts with If Not.
Code:
Private Sub My_Age_Change() If Not End Sub
Step 5: Ask compiler to check if the value of TextBox “My_Age” is numeric or not using IsNumeric Keyword in front of If Not condition.
Code:
Private Sub My_Age_Change() If Not IsNumeric(My_Age.Value) End Sub
Step 6: As every IF condition asks, add a Then Keyword for it.
Code:
Private Sub My_Age_Change() If Not IsNumeric(My_Age.Value) Then End Sub
In this piece of code, you are asking the compiler to check whether the value inputted by the user under TextBox is numeric or not. If, it’s not numeric, then what to do? Follow the next step.
Step 7: With the use of MsgBox, add a comment like “Sorry! Only Numbers are Allowed.” to be displayed if, the value in TextBox is not numeric.
Code:
Private Sub My_Age_Change() If Not IsNumeric(My_Age.Value) Then MsgBox "Sorry! Only Numbers are Allowed." End Sub
Step 8: Now, close this IF statement by using keyword End IF.
Code:
Private Sub My_Age_Change() If Not IsNumeric(My_Age.Value) Then MsgBox "Sorry! Only Numbers are Allowed." End If End Sub
Step 9: Now, run this code by hitting the F5 button from your keyboard or Run button from VBE and see the output. You’ll get a TextBox as shown below:
Now, I try to input an Integer value in it and see whether it actually allows me to input the same or not.
Bingo, it allows me to store the integer value.
Now, let’s try to add some float (Value with Decimal points) to this text box and see whether it allows us to input the same.
Let’s check if this text box allows us to store some negative values under it or not. I will type some negative number and check.
Well! Well!! Well!!!, it shoots a message me saying only numbers are allowed. It happened because this code written initially is with IF condition, it doesn’t have any workaround what to do if the number is non-positive. Moreover, Age can’t be negative. Right? Therefore, I am getting a warning message as you can see above. However, If you click OK in the message box and try inputting the number, it will allow you to do so. But I will not recommend breaking the rules over there.
Finally, let’s try some text string in the box and see if it allows the same to be stored under textbox or not.
As rightly expected, as soon as I entered the first string letter, the system prevented me to do so by throwing a message. This is because we have mentioned a condition in our code saying if the value is not numeric, please throw a message towards the user saying only numeric values are allowed.
Things to Remember
- VBA TextBox is a special option under UserForm. Which is designed to take input from the user itself.
- There are two methods to set the values for a TextBox: by using TextBox.Text and by using TextBox.Value.
- Sometimes it’s mandatory to restrict the user input so that we are getting the right type of data.
Recommended Articles
This is a guide to VBA TextBox. Here we discuss how to Create TextBox in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –
