I was surprised to see that Find and Replace was not “working” on text in a Text Box, so I embarked on a search for a solution. This solution loops through all textboxes, searching for a string and replacing it with another string similar to the Find and Replace (Ctrl + H) feature.
In this tutorial:
Required knowledge:
1. Find ’em
This first piece of code is pilfered directly from Word.tips.net [1] It iterates through all the Text Boxes in the document and displays an excerpt of the text content in a MsgBox.
' https://word.tips.net/T003520_Finding_Text_Boxes.html Sub SearchTextBox() Dim shp As Shape Dim sTemp As String Dim iAnswer As Integer For Each shp In ActiveDocument.Shapes If shp.Type = msoTextBox Then shp.Select Selection.ShapeRange.TextFrame.TextRange.Select sTemp = Selection.Text sTemp = Left(sTemp,20) iAnswer = MsgBox("Box contains text beginning with:" & vbCrLf _ & sTemp & vbCrLf & "Stop here?", vbYesNo, "Located Text Box") If iAnswer = vbYes Then Exit For End If Next End Sub
vbCrLf
is similar to pressing Enter. It represents a carriage-return character combined with a linefeed character for print and display functions.
Note the use of the Left function (see Excel’s LEFT() function) to only display the first 20 characters of the Text Box’s text in the MsgBox in case the text is extremely long.
2. Replace
We now need to add 2 variables to hold the string we are searching for and the string we are replacing it with, searchFor and replaceWith. We will use VBAs InputBox to get the values for those variables. [5]
We also want to check if searchFor exists in the text in the textbox. We use the InStr function for this.
Sub SearchReplaceTextBox() Dim shp As Shape Dim sTemp As String Dim iAnswer As Integer Dim searchFor As String Dim replaceWith As String searchFor = InputBox("Enter the string you want replaced: ") replaceWith = InputBox("Enter the string you want to replace " & searchFor & " with:") For Each shp In ActiveDocument.Shapes If shp.Type = msoTextBox Then shp.Select Selection.ShapeRange.TextFrame.TextRange.Select sTemp = Selection.Text If (InStr(1, sTemp, searchFor)) Then ' set the textbox text to the returned string Selection.ShapeRange.TextFrame.TextRange = sTemp iAnswer = MsgBox("Box contains the search text: " & searchFor & vbCrLf _ & Left(sTemp, 20) & vbCrLf & "Stop here?", vbYesNo, "Located Text Box") If iAnswer = vbYes Then Exit For Else iAnswer = MsgBox(searchFor & " does not appear in this textbox." & vbCrLf _ & "Stop here?", vbYesNo, "Located Text Box") If iAnswer = vbYes Then Exit For End If End If Next End Sub
3. Finishing touches
We can never trust the user’s input!
Sub SearchReplaceTextBox() Dim shp As Shape Dim sTemp As String Dim iAnswer As Integer Dim searchFor As String Dim replaceWith As String searchFor = InputBox("Enter the string you want replaced: ") replaceWith = InputBox("Enter the string you want to replace " & searchFor & " with:") If Len(searchFor) = 0 Or Len(replaceWith) = 0 Then MsgBox "Both pieces of information are required." Exit Sub ElseIf searchFor = replaceWith Then MsgBox "That is pointless - the strings are the same." Exit Sub End If For Each shp In ActiveDocument.Shapes If shp.Type = msoTextBox Then shp.Select Selection.ShapeRange.TextFrame.TextRange.Select sTemp = Selection.Text If (InStr(1, sTemp, searchFor)) Then ' do the actual string replacement sTemp = Replace(sTemp, searchFor, replaceWith) ' set the textbox text to the returned string Selection.ShapeRange.TextFrame.TextRange = sTemp iAnswer = MsgBox("This textbox contains the search text: " & searchFor & vbCrLf _ & Left(sTemp, 20) & vbCrLf & "Stop here?", vbYesNo, "Located Text Box") If iAnswer = vbYes Then Exit For Else iAnswer = MsgBox(searchFor & " does not appear in this textbox." & vbCrLf _ & "Stop here?", vbYesNo, "Located Text Box") If iAnswer = vbYes Then Exit For End If End If Next End Sub
References:
- Wyatt, A. (no date) Finding Text Boxes, tips.net. Available at: https://word.tips.net/T003520_Finding_Text_Boxes.html (Accessed: 3 March 2024).
- o365devx (no date) Finding and Replacing Text or Formatting, Microsoft Learn. Available at: https://learn.microsoft.com/en-us/office/vba/word/concepts/customizing-word/finding-and-replacing-text-or-formatting#using-the-replacement-object (Accessed: 10 March 2024).
- o365devx (no date) Replace function (Visual Basic for Applications), (Visual Basic for Applications) | Microsoft Learn. Available at: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/replace-function (Accessed: 10 March 2024).
- Solved: Update text in a Shape with VBA: Experts Exchange (2022) Solved: Update text in a Shape with VBA | Experts Exchange. Experts Exchange. Available at: https://www.experts-exchange.com/questions/28211963/Update-text-in-a-Shape-with-VBA.html (Accessed: 10 March 2024).
- o365devx (no date) InputBox function (Visual Basic for Applications), (Visual Basic for Applications) | Microsoft Learn. Available at: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/inputbox-function (Accessed: 10 March 2024).