Userform VBA without opening worksheet

[Akbarov]

Hey community,

Can anyone help me please, i use following formula ( userform ) to search and add product to worksheet. Product list is in Stock Data sheet,and it adds found product to Form sheet. but when i click search it is opening Stock Data sheet, can it find and add product without opening Stock Data sheet?

Private Sub cmdSearch_Click()


Dim RowNum As Long
Dim SearchRow As Long


RowNum = 2
SearchRow = 2


Worksheets("Stock Data").Activate


Do Until Cells(RowNum, 1).Value = ""


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


If SearchRow = 2 Then
    MsgBox "Not found!"
    Exit Sub
End If


lstSearchResults.RowSource = "SearchResults"

 

[PatOBrien198]

How 'bout:

Hey community,

Private Sub cmdSearch_Click()


Dim RowNum As Long
Dim SearchRow As Long
Dim wsSD as Worksheet

set wsSD  = Worksheets("Stock Data")

RowNum = 2
SearchRow = 2



Do Until wsSD.Cells(RowNum, 1).Value = ""


    If InStr(1, wsSD.Cells(RowNum, 2).Value, txtKeywords.Value, vbTextCompare) > 0 Then
        Worksheets("Product Search").Cells(SearchRow, 1).Value = wsSD.Cells(RowNum, 1).Value
        Worksheets("Product Search").Cells(SearchRow, 2).Value = wsSD.Cells(RowNum, 2).Value
        Worksheets("Product Search").Cells(SearchRow, 3).Value = wsSD.Cells(RowNum, 3).Value
        SearchRow = SearchRow + 1
    End If
    RowNum = RowNum + 1
Loop


If SearchRow = 2 Then
    MsgBox "Not found!"
    Exit Sub
End If


lstSearchResults.RowSource = "SearchResults"

NOTE: I DID NOT TEST THIS

 

[Fluff]

Another (untested) option

Private Sub cmdSearch_Click()


Dim RowNum As Long
Dim SearchRow As Long


RowNum = 2
SearchRow = 2


With Worksheets("Stock Data")


Do Until .Cells(RowNum, 1).Value = ""


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

If SearchRow = 2 Then
    MsgBox "Not found!"
    Exit Sub
End If


lstSearchResults.RowSource = "SearchResults"

 

[Akbarov]

Works thanks both

 

[Fluff]

Glad we could help & thanks for the feedback