UserForm search in the background

[Want2BExcel]

I need help. I have this UserForm that basically works ok. Problem is that when I hit search while I'm on Sheet1, I get moved to the sheet Text Values and then back as I hit the Add button. I don't want to leave Sheet1. The search has to be performed in the background. How do I do that?

Private Sub cmdAdd_Click()

    Dim ListBoxRow As Long
    Dim ws As Worksheet


Worksheets("Sheet1").Activate

ListBoxRow = lstSearchResults.ListIndex + 2
ActiveCell.Cells = Worksheets("Search").Cells(ListBoxRow, 1).Value
 

End Sub

Private Sub cmdSearch_Click()

Dim RowNum As Long
Dim SearchRow As Long

RowNum = 2
SearchRow = 2

Worksheets("Text Values").Activate

Do Until Cells(RowNum, 1).Value = ""
    If InStr(1, Cells(RowNum, 1).Value, txtKeywords.Value, vbTextCompare) > 0 Then
        Worksheets("Search").Cells(SearchRow, 1).Value = Cells(RowNum, 1).Value
        SearchRow = SearchRow + 1
    End If
    RowNum = RowNum + 1
Loop

If SearchRow = 2 Then
    msgbox "No Match", , "Error"
    Exit Sub
End If

lstSearchResults.RowSource = "SearchResults"

End Sub

Private Sub UserForm_Initialize()
' This code runs when form is initialized.
txtKeywords.SetFocus
Worksheets("Search").Range("A2:A100").ClearContents

    ' Set Initial Values
End Sub

 

[mikerickson]

You could change the routine to not Active worksheets.

With Worksheets("Text Values")

    Do Until .Cells(RowNum, 1).Value = ""
        If InStr(1, .Cells(RowNum, 1).Value, txtKeywords.Value, vbTextCompare) > 0 Then
            Worksheets("Search").Cells(SearchRow, 1).Value = .Cells(RowNum, 1).Value
            SearchRow = SearchRow + 1
        End If
        RowNum = RowNum + 1
    Loop
End With

If SearchRow = 2 Then
    msgbox "No Match", , "Error"
    Exit Sub
End If

 

[Want2BExcel]

You could change the routine to not Active worksheets.

With Worksheets("Text Values")

    Do Until .Cells(RowNum, 1).Value = ""
        If InStr(1, .Cells(RowNum, 1).Value, txtKeywords.Value, vbTextCompare) > 0 Then
            Worksheets("Search").Cells(SearchRow, 1).Value = .Cells(RowNum, 1).Value
            SearchRow = SearchRow + 1
        End If
        RowNum = RowNum + 1
    Loop
End With

If SearchRow = 2 Then
    msgbox "No Match", , "Error"
    Exit Sub
End If

I could...and I did now...and it works! Thank you

....I'm totally newbie and I had not thought of "With" at all