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?
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:
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!