ActiveX controls – TextBox in Excel VBA

Last Updated on January 11, 2023 by token

How to insert VBA TextBox in Excel Sheet (ActiveX control)

VBA textbox field is used to enter data and then to process them with VBA code. Inserting a TextBox field into Excel sheet starts with selecting it in the Developer tab from the ActiveX controls. 

You can add more than one textbox fields. The best way to make boxes the same size is to create one text box and then copy and paste it. 

Now let’s do quick example using VBA TextBox. Try to create two text boxes in your Excel spreadsheet. First field will be used for writing your first name. Second field will be used for your last name. You can also look at properties of your text boxes. Just right-click on them and make sure, that boxes names are: TextBox1 and TextBox2.

Let also create on your sheet one CommandButton named Done and set it on the right. You can enter the button’s name in the Caption field  by selecting the button’s properties after right click.

Programming VBA TextBox (ActiveX control)

Application already has 2 VBA textbox fields. Our goal is to write a program that, after entering data into fields, will enter them into the A7 and B7 fields of excel sheet. After entering the data, the VBA MsgBox window will be displayed with a simple message. Now let’s click twice on the CommandButton Done. You need to check, that Design Mode in Developer’s Tab is active. You will be redirect to VBA Editor and you will probably see this code:

Private Sub CommandButton1_Click()
End Sub

Now change this code and add reference to TextBox1 and TextBox2, that you created before.

Private Sub CommandButton1_Click()
  
   Range("A7") = TextBox1
   Range("A8") = TextBox2
   MsgBox ("Done")
End Sub

Now you can back to your Excel Sheet. Deactivate Design Mode in Developer’s Tab and Try to fill text boxes and click Done button.

How to insert VBA TextBox in User Form (ActiveX control)

If you want to insert VBA TextBox field in UserForm, all you have to do is create new UserForm in VisualBasic Editor. When the UserForm is already created, click on TextBox icon in ToolBox. Create also one Command Button and change its name to Done. From ToolBox you can also use Label Control to create labels on UserForm – First name and Last name.

After that try to right-click on boxes and enter the Properties window. Make sure, that boxes names are: TextBox1 and TextBox2.

Create also CommandButton named Done. VBA code will be similar like in previous example.

Private Sub CommandButton1_Click() 
  Range("A7") = TextBox1 
  Range("A8") = TextBox2 
  MsgBox ("Done")
End Sub

When your Form is created, type F5 button to check your program.