VBA looping through Text Boxes in Word

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.

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:

1. Wyatt, A. (no date) Finding Text Boxes, tips.net. Available at: https://word.tips.net/T003520_Finding_Text_Boxes.html (Accessed: 3 March 2024).
2. 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).
3. 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).
4. 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).
5. 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).