Text box ActiveX control in Excel
In this tutorial, you will see how to insert text box ActiveX control in an Excel sheet.
We will also show how to write VBA code to fill text in the text box control.
How to insert text box control in an Excel sheet?
Follow these steps to insert a Text Box control into an Excel sheet:
In the “DEVELOPER” tab, locate the “Insert” button group:
There, look for the “Text Box” control in the Insert button group, click it:
Add the textbox in the Excel sheet by “drag-drop” the mouse as per the required size:
Writing text in the Text box
If you want to write text in the Text box while in the Excel sheet, just “Unselect” the “Design Mode”
Now you will be able to write text in the text box as you click inside it.
Writing text by using VBA code
In this example, we will write text into the Text box as you click the Command Button (ActiveX control).
For that, we need to write the VBA code in the click event of the button.
First of all, add a button and a text box in the sheet:
To learn more about buttons and how to change their captions, go to its tutorial: VBA/Excel Command Button
Ensure, “Design Mode” is active. Double-click on the button to open the VBA code editor:
(You may also right-click on the button and press the “View Code” option to open the VBA editor)
Write the following line of code in the click event of the button:
Save the code and get back to the Sheet.
Step 4:
Click on the button and you will see the Text box is populated with the text we used in the VBA code:
Assign the text box’s text to a cell example
In this example, we have written some text in our Textbox. The task is to assign that text to the B5 cell as we click on the button.
Write some text after adding the Text Box control in the Excel sheet. Also, add a Command Button control in the sheet:
Double-click on the button to open the Code editor and write this line of code:
Range("B5").Value = "Write this text to B5 Cell"
Save the code and get back to the sheet.
Click on the button and you should see the Text box’s text is assigned to the B5 cell:
Write same text in the Sheet cell as in the Text Box typed
In this example, we will update the text in the A3 cell live i.e. as you type in the textbox, the text in the cell will also be changed to the same text as in the textbox control.
For that, we will write code in the textbox’s TextBox_Change() event.
Follow these steps.
Add a textbox in the sheet and double-click on it to open the editor.
It should automatically take you to the TextBox_Change() event code section.
Write this line of code:
Range("A3").Value = TextBox1.Text
Save the code and get back to the sheet.
Start typing in the textbox after unselecting the “Design Mode” and you will see the text is also updated on the A3 cell:
Writing to more than one cells
Similarly, you may write to many cells by providing a range of cells to the VBA range object rather than just one cell.
Just replace the above example’s code with this and the same text will be written to A2 to A7 cells:
