In this tutorial, I am going to share with you 3 effective ways to add text to a Textbox using Excel VBA. You can easily apply these methods in any set of data to add new texts to Textboxes. To achieve this task, we will also see some useful features that might come in handy in many other Excel related tasks.
Download Practice Workbook
You can download the practice workbook from here.
3 Effective Ways to Add Text to Textbox Using Excel VBA
We have taken a concise dataset to explain the steps clearly. The dataset has approximately 7 rows and 2 columns. Initially, we are keeping all the cells in General format. For all the datasets, we have 2 unique columns which are Employee Name and Location (TextBox). Although we may vary the number of columns later on if that is needed.
1. Typing Value in VBA to Add Text to Textbox in Excel
- First, go to the Insert tab and click on the Text Box under Text.
- Now click on the cell where you want to place the Textbox.
- As a result, this will give you an empty Textbox as in the image below.
- Now, go to the Developer tab and click on Visual Basic.
- Then, in the Module window enter the following code:
Public Sub TypingValue() ActiveSheet.Shapes("TextBox 2").TextFrame.Characters.Text = "California" End Sub
In the above code, ActiveSheet is referencing the worksheet that we are currently in. Next, Shapes(“TextBox 2”) selects the Textbox to work with. TextFrame allows us to manipulate the elements of the Textbox. The Characters command allows us to format characters in a text string.
- After that, go to the Developer tab and click on Macros.
- Now, in the new Macro window, select the macro TypingValue and click on Run.
- Consequently, this will add the text California inside the Textbox as we set in the above macro.
- Similarly, you can add any other value you want inside other text boxes.
So in this way, you can quickly add any type of text inside a Textbox.
2. Using Range Property in VBA
We can also add new text inside a Textbox by using the Range property inside VBA. Let us see how to achieve this.
- To begin with, insert a Textbox within your dataset as previously.
- Now, open the VBA module window as we did before in Method 1 and insert the following code:
Public Sub RangeProperty() ActiveSheet.Shapes("TextBox 1").TextFrame.Characters.Text = Range("D5").Value End Sub
In the above code, almost all of the methods are similar to what we saw in the previous method. Except for the Range(“D5”).Value portion which takes the range of cells that holds the texts that we want to add to the Textbox.
- Then, open the macro window and click on Run to run the VBA.
- Immediately, this should add the appropriate text inside the empty Textbox.
- Finally, you can follow the same procedure to add as many Textboxes as you want inside your worksheet.
So, as we can see, this method is also very useful to add texts inside a Textbox.
3. Applying VBA If Statement
In this final method, we will see how we can use the If statement inside VBA and with that add a new text inside an empty Textbox. Below are the detailed steps.
- To begin with this method, follow the previous method and add a new Textbox inside the data table.
- Next, type the following code in the VBA module window: Please follow the steps of Method 1 if you need to know how to open a VBA module.
Sub VBAIfStatement() Sheet_Name = "VBAIF" Shape_Name = "TextBox 2" To_be_Replaced = " " Replaced_with = "California" If InStr(1, Sheets(Sheet_Name).Shapes(Shape_Name).TextFrame.Characters.Text, To_be_Replaced) <> 0 Then Sheets(Sheet_Name).Shapes(Shape_Name).TextFrame.Characters.Text = Replace(Sheets(Sheet_Name).Shapes(Shape_Name).TextFrame.Characters.Text, To_be_Replaced, Replaced_with) End If End Sub
In the above code, the If-Then statement is checking whether a given condition is true or not. Based on this it will perform certain actions. Next, the InStr method determines the first occurrence of one string within another string. The End If command terminates the If statement operation.
- Then, select the VBAIfStatement macro as we did earlier and click on Run.
- As a result, the VBA code will add the new text inside the empty Textbox as in the image below.
- Lastly, do this for all of the remaining text boxes where you want to add any new text.
At last, we are able to successfully add the texts that we desired in the appropriate Textbox.
I hope that you were able to apply the methods that I showed in this tutorial on how to add text to a Textbox using Excel VBA. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more Excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.