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

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.

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

In this first method, we will be typing the new text value to add directly inside the VBA code in Excel and then running the code. Follow the steps below to do this.

Steps:

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.

Next, in the new window, click on Insert and select Module.

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.

Steps:

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

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 checks 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 desire in the appropriate Textbox.

Download Practice Workbook

You can download the practice workbook from here.

Add Text to Textbox.xlsm

Conclusion

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. If you have any queries, please let me know in the comments.

Get FREE Advanced Excel Exercises with Solutions!

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

3 Effective Ways to Add Text to Textbox Using Excel VBA

1. Typing Value in VBA to Add Text to Textbox in Excel

2. Using Range Property in VBA

3. Applying VBA If Statement

Conclusion

Related Articles

Leave a reply Cancel reply