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

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.


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.

excel vba add text to textbox


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.

inserting textbox to add text to a Textbox using Excel VBA

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

opening visual basic to add text to a Textbox using Excel VBA

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

inserting module to add text to a Textbox using Excel VBA

  • Then, in the Module window enter the following code:
Public Sub TypingValue()
ActiveSheet.Shapes("TextBox 2").TextFrame.Characters.Text = "California"
End Sub

macro code to add text to a Textbox using Excel VBA

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.

opening macros to add text to a Textbox using Excel VBA

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

Read More: How to Convert TextBox Value to Cell with VBA in Excel


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

Read More: How to Use VBA Textbox Properties in Excel (with Easy Steps)


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

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


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. Lastly, to learn more Excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.


Related Articles

Nazmul Hossain Shovon

Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo