UserForm search in the background

Want2BExcel

Board Regular
Joined
Nov 24, 2021
Messages
114
Office Version
  1. 2016
Platform
  1. Windows
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?

VBA Code:
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

VBA Code:
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

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

    ' Set Initial Values
End Sub
 

Attachments

  • UserFormAll.JPG
    UserFormAll.JPG
    86.5 KB · Views: 13

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
You could change the routine to not Active worksheets.
VBA Code:
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
 
Upvote 0
Solution
You could change the routine to not Active worksheets.
VBA Code:
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 :LOL:
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top