How to find and replace specific text in text boxes?
Find and Replace is a useful tool for us to find and replace cell contents or formulas quickly and conveniently in a worksheet. But now, I have multiple text boxes in my worksheet, and I want to find the specific text and replace it with other value in these text boxes only. Do you have any good ways to deal with this problem?
Find and replace specific text in multiple text boxes
Find and replace specific text in multiple text boxes
You can find and replace the text that you need manually one by one, but if there are multiple text boxes, this will be boring and tired. So the following VBA code can help you finish it at once.
1. Activate your worksheet, and hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Find and replace specific text in text boxes of current worksheet
Sub TextBoxReplace()
'Updateby20140603
Dim xWs As Worksheet
Dim shp As Shape
Dim xFindStr As String
Dim xReplace As String
xFindStr = Application.InputBox("Find:", xTitleId, "", Type:=2)
xReplace = Application.InputBox("Replace with:", xTitleId, "", Type:=2)
Set xWs = Application.ActiveSheet
On Error Resume Next
For Each shp In xWs.Shapes
xValue = shp.TextFrame.Characters.Text
shp.TextFrame.Characters.Text = VBA.Replace(xValue, xFindStr, xReplace, 1)
Next
End Sub
3. Then press F5 key to run this code, and a prompt box will pop out to remind you entering your old text that you want to replace. See screenshot:
4. Go on clicking OK, and in this prompt box, type the new text value that you want, see screenshot:
5. And then click OK button, the old values have been replaced with the new values that you entered in the current worksheet. See screenshots:
 |
 |
 |
Related articles:
How to find and replace text in chart titles in Excel?
How to find and replace text within comments in Excel?
How to change multiple hyperlink paths at once in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
