WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA TextBox

VBA TextBox

Excel VBA TextBox

Textbox is simply like a box which is used to get input from a user, text boxes are a part of user forms and in developer tab in any excel worksheet, If we want to make text boxes in a user form we can select the textbox option from user form controls in VBA or in worksheet we can select it from the design tab.

VBA TextBox is one of the controls of the many controls of the User Form. By displaying the text box on the user form, we can ask them to enter the data into the text box, and data entered by the user can be stored to the worksheet with simple codes.

User forms are very attractive in VBA coding. It helps us immensely, especially when we need to get input from the users. With user forms, we have many controls, and to get the input value from the users, “Text Box” is the ideal option in a user form. By putting a text box on the user form, we can actually tell the user to enter the required value in the text box we are displaying. If you have no idea about VBA text boxes, then this article will take a tour of VBA text boxes.

VBA Text Box

How to Insert TextBox in VBA UserForm?

You can download this VBA Text Box Template here – VBA Text Box Template

In order to insert a text box first, we need to insert user form. To insert user form, go to Insert > User Form.

Insert Userform

As soon you click on User Form, we will User Form just like another module.

Userform controls

Click on User Form to see the Controls of the User Form.

VBA Text Box Userform 1-1

From this Controls toolbox, select “TextBox” and drag on the User Form.

VBA Text Box control

With this, we can see many properties associated with this text box.

TextBox Properties

Press the F4 key to see the Properties of the Text Box.

TBox Properties

As we can see, the text box has Name, color, Border like this many. Now give a Proper Name so that we can easily refer to this text box while coding.

Change Name Property

Example of using TextBox in Userform

We will conduct one of the projects with VBA text. We will create a data entry user form to store the details of the employees.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Step 1: On the user form, draw Label.

VBA TextBox step 1

Step 2: Change the default text of the label to “Employee Name.”

VBA TextBox step 2

Step 3: In front of the Label, draw a text box.

VBA TextBox step 3

Step 4: Give a proper name to the text box as “EmpNameTextBox.”

VBA TextBox step 4

Step 5: Now draw one more Label below the first label and enter the text as “Employee ID.”

VBA TextBox step 5

Step 6: In front of the second label, draw one more text box and name it as “EmpIDTextBox.”

VBA TextBox step 6

Step 7: Draw one more label and enter the text as “Salary.”

VBA TextBox step 7

Step 8: Draw one more text box in front of the “Salary” label and name it as “SalaryTextBox.”

VBA TextBox step 8

Step 9: Now insert “Command Button” from the ToolBox.

VBA TextBox step 9

Step 10: Change the text of the Command Button as “Submit.”

VBA TextBox step 10

Ok, we are done with the User Form design part. Now we need to write the code to store the data entered in this user form. As of now, run the user form by pressing the F5 key. We should see a user form like this.

VBA TextBox step 11

Step 11: Change the Caption of the User Form in the Properties window.

VBA TextBox step 11

Step 12: Now double click on the Submit Command Button.As soon as you double click you will see this auto sub procedure like the below.

VBA Text Box step 12

This is when you click on the Submit button, what should happen. We need to mention the tasks in the VBA code. In this project, our aim is to store the data entered in the text box as soon as we click on the Submit Button.

For this, first, create a Template like this in the worksheet named “Employees Sheet.”

VBA Text Box step 12.1

Step 13: Now come back to the visual basic editor. Inside the button, click subroutine in VBA first to determine the last used row by using the below code.

Code:

Private Sub CommandButton1_Click()

   Dim LR As Long

   LR = Worksheets("Employee Sheet").cell(Rows.Count, 1).End(xlUp).Row + 1

End Sub

VBA Text Box step 13

Step 14: First thing is in the first column we will store Employee Name. So for this, we need to access the text box named “EmpNameTextBox.”

Code:

Private Sub CommandButton1_Click()

   Dim LR As Long

   LR = Worksheets("Employee Sheet").cell(Rows.Count, 1).End(xlUp).Row + 1

   Ramge("A" & LR).Value = EmpNameTextBox.Value

End Sub

VBA Text Box step 14

Step 15: In the second column, we need to store Employee ID. So this will be obtained by accessing the text box named “EmpIDTextBox.”

Code:

Private Sub CommandButton1_Click()

   Dim LR As Long

   LR = Worksheets("Employee Sheet").cell(Rows.Count, 1).End(xlUp).Row + 1

   Ramge("A" & LR).Value = EmpNameTextBox.Value

   Ramge("B" & LR).Value = EmpIDTextBox.Value

End Sub

VBA Text Box step 15

Step 16: At last, we need to store the salary part. For this, we need to access to text box named “SalaryTextBox.”

Code:

Private Sub CommandButton1_Click()

   Dim LR As Long

   LR = Worksheets("Employee Sheet").cell(Rows.Count, 1).End(xlUp).Row + 1

   Ramge("A" & LR).Value = EmpNameTextBox.Value

   Ramge("B" & LR).Value = EmpIDTextBox.Value

   Range("C" & LR).Value = SalaryTextBox.Value

End Sub

VBA Text Box step 16

Ok, we are done with the coding part as well. Now run the code using the F5 key. We should see a User Form like the below.

Userform Design

As of now, all the boxes are empty.

Fill the details first.

Enter Details

Now click on the “Submit” button. It will store the data on the worksheet.

Output

Like this, you can keep entering the data and hit and submit button. This is the simple data entry user form with a text box.

Recommended Articles

This has been a guide to VBA TextBox. Here we learn how to insert and use textbox control in user form through excel VBA along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –

  • VBA Close UserForm
  • Excel VBA ArrayList
  • Insert Row using VBA
  • Using Not Equal in VBA
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Text Box Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More