Zehad Rian Jim
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.
- Next, create the Text box according to your wish.
- After that, write the necessary texts in the text box.
- Thereafter, go to Insert>Module options to create a VBA window.
- Moreover, insert the following formula in the VBA window.
Sub ChangingTextbox()
Sheets("Changing Textbox").Shapes("textbox 1").TextFrame.Characters.Text = "Ron"
End Sub
- Finally, if you press the Run option or F5 button, then you will get the final result.
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.
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.
