How to Use VBA Textbox Properties in Excel (with Easy Steps)

Oftentimes, we may need to insert a TextBox to leave a helpful note for other users so they can easily understand the process. However, TextBoxes are very useful when it is necessary to show a large text in Excel. Moreover, they can be resized and moved around easily. In this article, I will show you the textbox properties with an example using VBA in Excel. Hence, read through and save time.

What Is VBA TextBox?

In general, a TextBox is a box that receives data from the user. It is a part of UserForm. The UserForm TextBox in Excel VBA is one of the UserForm controls in the TextBox. In addition, 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. Moreover, the data type UserForm is an Object. However, variables can be defined as type UserForm before being assigned to an instance of a UserForm type created at design time. Afterward, the UserForm Textbox is used to show text or to modify the text that is already displayed in the TextBox.

Easy Steps to Insert TextBox in VBA UserForm

In Microsoft Excel, a UserForm is a custom-created dialog box that enables user information entering more manageable and user-friendly. However, 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. Hence, go through the following steps in order to insert a TextBox in VBA UserForm.

📌 Steps:

Firstly, hold the Alt + F11 keys in Excel, which opens the Microsoft Visual Basic Applications window.
Secondly, click the Insert button and select UserForm from the menu to create a user form.

Thirdly, by doing this, you can see the UserForm is created, and a Toolbox will appear aside.

Fourthly, there seem to be a variety of handy controls in the Toolbox. Now, I will employ a TextBox.

Lastly, a “+” icon will appear, and drag the icon as per the requirement to create the TextBox.

Properties of VBA TextBox in Excel

Usually, a TextBox may contain specific properties, and you can quickly obtain them. However, you have to hit the F4 key in order to get the properties of TextBox that you have created already.

From the picture above, you can see that it contains several properties, including Name, Color, Border Color, Border Style, Cycle, Font, Height, Picture Alignment, ScrollBars, Tag, Width, Zoom, and many more. Here, I have changed the Name and Caption of the TextBox.

Step-by-Step Procedure for Using VBA TextBox Properties in Excel

In this part, I will show you the use of TextBox properties with VBA in Excel. Hence, read through the steps mentioned below in order to complete the operation properly.

📌 Step 1: Select Dataset for Excel VBA TextBox Properties

For the purpose of demonstration, I have used the following sample dataset. However, it contains the Employee Name and Location, which I will generate from the VBA TextBox.

📌 Step 2: Draw Label and TextBox in Excel VBA

Initially, select Label from the Toolbox to draw a Label on the UserForm.

Now, insert a TextBox and change the Label name accordingly.

From the TextBox Properties, change the name of the TextBox.

Similarly, draw one more Label and a TextBox.
In addition, Name the Label as Location and the TextBox as EmployeeLocationTextBox.

Again, go to the Toolbox and select CommandButton.

Next, change the name of the button to Submit.

Afterwards, press the F5 key to run the UserForm and it will look like the image below.

📌 Step 3: Insert VBA Code to TextBox

In the beginning, go to the UserForm and double-click the Submit button.

Then, it will show an auto sub like the following image.

After that, write the following code into the Visual Basic Editor.

Private Sub CommandButton1_Click()
  Range("B5") = Me.EmployeeNameTextBox
  Range("C5") = Me.LocationTextBox
  Unload Me
End Sub

Finally, press the F5 key to run the code, and the Employee Info dialog box will appear.

📌 Step 4: Input Data with TextBox

First, input data according to your desire and click on the Submit button.

In the end, the data will automatically be inserted into your preferred Range.

Final Output

Last but not least, change the Range of the VBA code in order to change the data input location according to your choice. In addition, repeat Step 4 to add more data. Finally, the output will appear as below.

Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.

VBA Textbox Properties.xlsm

Conclusion

These are all the steps you can follow to format textbox properties using VBA in Excel. Overall, in terms of working with time, we need this for various purposes. I have shown multiple methods with their respective examples, but there can be many other iterations depending on numerous situations. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.

Get FREE Advanced Excel Exercises with Solutions!