Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

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


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. Afterwards, the UserForm Textbox is used to show text or to modify the text that is already displayed in the TextBox.

Excel VBA 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.

Insert TextBox in VBA UserForm

  • 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.

excel vba textbox properties

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.

excel vba textbox properties Dataset


๐Ÿ“Œ Step 2: Draw Label and TextBox in Excel VBA

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

Draw Label and TextBox in Excel VBA

  • 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.

excel vba textbox properties


๐Ÿ“Œ 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

Insert VBA Code to TextBox in Excel

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

excel vba textbox properties


๐Ÿ“Œ Step 4: Input Data with TextBox

  • At first, input data according to your desire and click on Submitย button.

Input Data with VBA TextBox in Excel

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

Read More: How to Add Text to Textbox Using Excel VBA (3 Effective Ways)


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.

excel vba textbox properties

Read More: How to Use VBA to Change Textbox Text in Excel (3 Easy Ways)


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.

For more information like this, visit ExcelDemy.com.


Related Articles

Mehedi Hasan

Mehedi Hasan

Hi, I am Mehedi. I have completed my B.Sc. from Bangladesh University of Engineering and Technology. I have a strong interest in innovation and research in the field of Data Science and Machine Learning. Gradually, I now understand the value of Data Analysis and I am trying to learn everyday.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo