VBA search function works only if triggered from another sheet

Question

This function loops through all the textboxes in the workbook to find and highlight text + textbox that contain the result.

The problem is: if I trigger it from a specific sheet, all the textboxes in that sheet that contain the result will NOT be highlighted (text is found though, so it works halfway).

If I trigger it from a worksheet that does not contain a textbox with a result, then everything works.

    Dim shp As Shape
    Dim Color As String
    Dim ColorIndexobj As String
    Dim Sizeobj As Integer
    Dim sFind As String
    Dim sFind2 As String
    Dim sTemp As String
    Dim iPos As Integer
    Dim sTemp2 As String
    Dim iPos2 As Integer
    Dim Response
    Dim sourceSheet As Worksheet
    Set sourceSheet = ActiveSheet

    sFind = InputBox("Search for?")
    If Trim(sFind) = "" Then
        MsgBox "Nothing entered"
        Exit Sub
    End If

    For Each ws In ActiveWorkbook.Worksheets
        ws.Select
            For Each shp In ws.Shapes
                If shp.Type = msoTextBox Then
        sTemp = shp.TextFrame.Characters.Text

If InStr(LCase(sTemp), LCase(sFind)) <> 0 Then

shp.Select
With shp.Line
        Color = shp.Line.ForeColor.RGB
        Weight = shp.Line.Weight
        .ForeColor.RGB = vbRed
        .Weight = 5
    End With

    sFind2 = LCase(sFind)
        sTemp2 = LCase(shp.TextFrame.Characters.Text)
        iPos2 = InStr(sTemp2, sFind2)
        If iPos2 > 0 Then
            With shp.TextFrame.Characters(Start:=iPos2, _
              Length:=Len(sFind2)).Font
        Sizeobj = .Size
                .Size = 35
            End With
        End If

    Set sourceSheet = ActiveSheet

              Response = MsgBox( _
              "Do you want to continue?", _
              Buttons:=vbYesNo, Title:="Continue?")

If Response = vbYes Then
With shp.Line
.ForeColor.RGB = Color
.Weight = Weight
End With

With shp.TextFrame.Characters(Start:=iPos2, _
Length:=Len(sFind2)).Font
.Size = Sizeobj
End With

End If


If Response = vbNo Then

With shp.Line
.ForeColor.RGB = Color
.Weight = Weight
End With

With shp.TextFrame.Characters(Start:=iPos2, _
Length:=Len(sFind2)).Font
.Size = Sizeobj
End With

Exit Sub
End If
End If
End If
Next
Next
Call sourceSheet.Activate
End Sub

Sorry, your code will need to be formatted so that it can be read. Please do that so that we can help you without wasting our time on indenting. — Variatus, May 11 '20 at 11:56
Your code is reasonably well-written, but it would be more polished and easier to read, debug and modify if you 1) Used `Option Explicit` at the top of all your modules to force yourself to declare all of your variables and 2) Use a consistent indentation scheme -- which is fairly easy to do in the VBA editor. — John Coleman, May 11 '20 at 12:26
sorry guys, you are absolutely right. I am not a coder, was just trying to adjust something I found on the internet! — Kuitogu67, May 11 '20 at 13:47

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

The problem is that the change that you are doing is undone before the window is ever repainted.

Here is a kludge (from this answer):

Immediately after

With shp.Line
    Color = shp.Line.ForeColor.RGB
    Weight = shp.Line.weight
    .ForeColor.RGB = vbRed
    .weight = 5
End With

Put the line:

ActiveWindow.SmallScroll 0

Exactly what causes a window to repaint during a running macro isn't clearly documented. The rules are evidently different as they apply to the active sheet, which would explain the behavior that you are observing. There isn't any simple RePaint method of a worksheet, hence the kludge, which works since scrolling triggers a repaint, even if the distance scrolled is zero.

From some reason, using DoEvents rather than this kludge doesn't seem to work.

Did the job :) Thanks, was getting me crazy! – Kuitogu67 May 11 '20 at 13:47 — Kuitogu67, May 11 '20 at 13:47

VBA search function works only if triggered from another sheet

1 Answers1