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

Get FREE Advanced Excel Exercises with Solutions!

This tutorial will demonstrate how to change Textbox text in Excel using VBA. Excel is a popular and useful tool for arranging and analyzing data. Oftentimes, we may need to insert a Text Box to leave a helpful note for other users so they can easily understand the process. Keeping this in mind, this article demonstrates the process of changing a Text Box Text in Excel. Throughout this tutorial, you will also learn some important Excel tools and techniques which will be very useful in any excel related task.


Download Practice Workbook

You can download the practice workbook from here.


3 Effective Methods to Use VBA to Change Textbox Text in Excel

We’ll use a sample dataset overview as an example in Excel to understand easily. If you follow the steps correctly, you should learn how to change Text Box text in Excel with VBA on your own. The steps are

1. VBA Changing Textbox Text in Excel

In this case, our goal is to change Text Box text in Excel with VBA. We will at first create Text Box text and then will try to change the whole text by using VBA code. The steps of this method are.

Steps:

  • First, go to Insert>Text>Text Box options.

Inserting Textbox to VBA Change Textbox Text in Excel

  • Next, create the Text box according to your wish.

Result to VBA Change Textbox Text in Excel

  • After that, write the necessary texts in the text box.

Writing Text to VBA Change Textbox Text in Excel

  • Then, choose the Visual Basic option in the Developer option.

Opening VBA Window to VBA Change Textbox Text in Excel

  • Thereafter, go to Insert>Module options to create a VBA window.

Opening New Module in VBA to VBA Change Textbox Text in Excel

  • Moreover, insert the following formula in the VBA window.
Sub ChangingTextbox()
Sheets("Changing Textbox").Shapes("textbox 1").TextFrame.Characters.Text = "Ron"
End Sub

Inserting VBA code to VBA Change Textbox Text in Excel

  • Finally, if you press the Run option or F5 button, then you will get the final result.

Result to VBA Change Textbox Text in Excel

Thus, we have changed the Text Box text in Excel with VBA. In this way, we can easily change any text with another text or text in the Text box in Excel.

Read More: How to Use VBA to Get Value from Userform Textbox in Excel


2. Replacing Letters to VBA Change Textbox Text

Now, we want to replace Text Box text in Excel with VBA by following the below steps.

Steps:

  • First, create a text box with proper text like Method 1.

  • After that, open the VBA window by following similar steps as Method 1.
  • Subsequently, insert the following formula in the VBA window.
Sub ReplacingLetter()
Sheet_Name = "Replacing Texts"
Shape_Name = "TextBox 1"
To_be_Replaced = "b"
Replaced_with = "a"
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

  • Last, you will get the result where you will find that all the “b” s are replaced with “a”. Thus, you will get the final result accordingly.

Final Result to VBA Change Textbox Text in Excel

Hence, we can easily replace Text Box text in Excel with VBA. We can easily replace any letter of the text with another letter by using this method.

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


3. Using Range Property to VBA Change Textbox Text

In this last portion, we want to use the range property in VBA to change Text Box text in Excel. We can fulfill this goal by following the below steps.

Steps:

  • To begin with, create a text box with proper text like Method 1.

  • In addition, in the B9 cell, insert the input you want to show in the text box.

  • Furthermore, open the VBA window by following similar steps as Method 1 and then insert the following formula in the VBA window.
Public Sub UsingRangeProperty()
ActiveSheet.Shapes("TextBox 1").TextFrame.Characters.Text = Range("B9").Value
End Sub

  • Finally, you will get a similar result in the text box as you have inserted input in the B9 cell.

So, we can change the Text Box text by taking the value from a certain cell in Excel with VBA.

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


Things to Remember

  • The file must be saved as Excel Macro-Enabled Workbook. Otherwise, the VBA code won’t work.
  • In this case, it is extremely important to remember that in the VBA code, you have to use the worksheet name properly. Otherwise, you won’t get the result accordingly.
  • In some cases, the already used file won’t give the correct results. So, we suggest using a fresh new workbook for these methods.
  • For every case, we suggest downloading the file from our article and going through the article while fulfilling the whole process.

Conclusion

Henceforth, follow the above-described methods. These methods will help you to change Text Box text in Excel with VBA. We will be glad to know if you can execute the task in any other way. Follow the ExcelDemy website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.


Related Articles

Zehad Rian Jim

Zehad Rian Jim

Hi there! I am Zehad Rian Jim. I graduated with a bachelor's degree in engineering from BUET. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include watching movies, tv series, and meeting new people. I also enjoy sports. My favorite sports are Cricket (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo