Finding and Replacing in Text Boxes

@J Stern
My Excel Toolbox's dynamic array function ListShapes is described here: https://excelribbon.tips.net/T009726_Positioning_a_Graphic_in_a_Macro.html
The list includes each shape's link formula. The ReplaceShapeText macro described in my comment below also identifes a shape's link formula. Neither attempts to modify the formula.

The ReplaceShapeText macro in My Excel Toolbox will find and offer to replace specified text in each shape on the active sheet or on all sheets of the active workbook. It includes Steve's logic to search grouped shapes plus options to match alphabetic case, whole words, and hidden shapes (such as hidden Comments). Text formatting is preserved.
This macro does not apply to chart labels but does apply to text boxes or other shapes added to charts.
Developing this macro presented several interesting user-interface challenges. Perhaps it should be revisited sometime with a UserForm.
See https://sites.google.com/view/MyExcelToolbox/

I should have included in my previous comment: The revised TextBoxReplace macro also affects Unthreaded Comments (Notes) because they are part of the Shapes collection. But curiously, in this case sNew will inherit the format of the character BEFORE the first character of sOld.

@Jonathon
With the following VBA, sNew will inherit the format of the first character of sOld. The remainder of the text box will retain its original format.

Sub TextBoxReplace()
    Dim shp As Shape
    Dim sOld As String
    Dim sNew As String
    Dim Compare, N
    Const CaseSensitive = vbBinaryCompare
    Const IgnoreCase = vbTextCompare

    'Change as desired
    sOld = "Old string"
    sNew = "New string"
    Compare = IgnoreCase
    
    For Each shp In ActiveSheet.Shapes
        N = 1
        With shp.TextFrame
            Do While N > 0
                N = InStr(N, .Characters.Text, sOld, Compare)
                If N > 0 Then
                    .Characters(N, Len(sOld)).Insert sNew
                    N = N + Len(sNew)
                End If
            Loop
        End With
    Next shp
End Sub

See https://sites.google.com/view/MyExcelToolbox/

Thanks for all of the info. This is great.

I have text boxes that have a variety of formatting within them, different color/font/size.


When I run this macro, it changes all of the formatting to be whatever the format is for the first words of the text box. Is there a way that all of the formatting in the text box can be retained?

Steves comment below was helpful for nested text boxes.

My problem - other nested text boxes have a formula that aren't being replaced.

ex of text box formula = ='CL Offsites Trend Table'!AW$4

trying to do a mass "find and replace" of just "CL Offsites" with a different name and this solution wont update these.

Thanks, Allen. This was a great tip. I found the "TextBoxReplace" macro has one limitation - it doesn't work with text box shapes that are nested inside groups. Apparently, Excel treats each group of shapes as a shape in itself. I looked online for how to handle that, and found an answer at "https://stackoverflow.com/questions/38314958/iterate-thought-all-the-even-if-grouped-shapes-in-a-sheet-excel-vba". Here is a modified routine that will check if each shape is a group, and if the shape in question is a group, it will loop through the subsidiary shapes within that group shape. Interestingly, it appears to handle nested groups just fine.

Sub TextBoxReplace()
Dim shp As Shape
Dim subshp As Shape
Dim sOld As String
Dim sNew As String

'Change as desired
sOld = "Old String"
sNew = "New String"

On Error Resume Next

For Each shp In ActiveSheet.Shapes
Debug.Print shp.Name

If shp.Type = msoGroup Then
For Each subshp In shp.GroupItems
Debug.Print Space(2) + subshp.Name
With subshp.TextFrame.Characters
.Text = Application.WorksheetFunction.Substitute( _
.Text, sOld, sNew)
End With
Next subshp
Else
With shp.TextFrame.Characters
.Text = Application.WorksheetFunction.Substitute( _
.Text, sOld, sNew)
End With
End If

Next

End Sub

John,
If you just select the text box or shape you can format as much as you like, font, size, colour, alignment. The only draw back is the whole box or shape has the same formatting. If you need a different format for a title or the like, create another text box, format that as you like, align the text boxes & group them together. This works in Excel 2007 & 365 so presumably everything in between.

HTH

Well Now - that's something I just learned - using a formula to place the text in a cell into a text box. After playing around with it for a while, I have found one problem - I cannot select the text in the text box to format it (size, colour, weight, font, etc.), and formaing the text in the source cell doesn't follow through to the representation in the text box. This is true whether I select the text within the source cell or the cell iteself for formating. The only way I have found, so far, is to use some of the tools on the Format tab on the ribbon, such as the word art gallery, and this provides no obvious way to change the size of the font. I haven't experimented with charts, to see if the same holds true there. My esperiments were done with Excel 2010 in Windows 10