Find TextBoxes in a Workbook and change background colour

  • Good morning,


    This is my first time posting and I am hoping someone can get me over a hurdle. I have a workbook with a dozen worksheets and each worksheet contains an image and several textboxes. I need to be able to loop through each worksheet in the workbook to find specific text in a textbox and once found, change the background color to yellow. The text in the text box could be repeated across several worksheets and I need each matching textbox to be colored. I found the following code that only works on a worksheet and makes the text bold and red:


    [VBA]Sub FindInShape2()
    On Error Resume Next


    Dim shp As Shape
    Dim sFind As String
    Dim sTemp As String
    Dim iPos As Integer
    Dim Response


    sFind = InputBox("Search for?")
    If Trim(sFind) = "" Then
    MsgBox "Nothing entered"
    Exit Sub
    End If
    sFind = LCase(sFind)
    For Each shp In ActiveSheet.Shapes
    sTemp = LCase(shp.TextFrame.Characters.Text)
    iPos = InStr(sTemp, sFind)
    If iPos > 0 Then
    With shp.TextFrame.Characters(Start:=iPos, _
    Length:=Len(sFind)).Font
    .ColorIndex = 3
    .Bold = True
    End With
    End If
    Next
    MsgBox "Finished"
    End Sub[/VBA]


    So I did some more looking and modified the above code and placed in a module:


    [VBA]Sub FindInTB()
    On Error Resume Next


    Dim wks As Worksheet, tb As TextBox
    Dim sFind As String
    Dim sTemp As String
    Dim iPos As Integer
    Dim Response


    sFind = InputBox("Search for?")
    If Trim(sFind) = "" Then
    MsgBox "Nothing entered"
    Exit Sub
    End If
    sFind = LCase(sFind)
    For Each wks In ActiveWorkbook.Worksheets
    For Each tb In wks.TextBoxes
    sTemp = LCase(tb.TextFrame.Characters.Text)
    iPos = InStr(sTemp, sFind)
    If iPos > 0 Then
    With tb.TextFrame.Characters(Start:=iPos, _
    Length:=Len(sFind)).Font
    .ColorIndex = 3
    .Bold = True
    End With
    End If
    Next tb
    Next wks
    MsgBox "Finished"
    End Sub[/VBA]


    When I step through it, I see it looping through each worksheet, but it does not change the formatting of the textboxes. What am I missing? Your help is appreciated. Thank you.

  • Try this. The On Error hides errors so you can't see what's going wrong. In your second bit of code you switched from Shapes to Textboxes. Stick to Shapes and you can then check the type of shape before checking the text.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!