2 Workbooks, one VBA UserForm

[TechAgent007]

So, somehow I was able to get the list to show up like I wanted. then I wanted to do better, deleted the code aaaaaaaaaand it won't work. Now I can't even get the RowSource to work with the Name Manger dynamic range I set up. At one point I could get a click event to run but not return anything. No errors. Now nothing. I've added an image to give you an idea. I will not be able to upload the code as it contains work sensitive data locations (customer data)


This user form works PERFECT for entering data into the "database." I just updated it to prevent blank entries. So I CAN call external locations on a click event.

I want to open the form and have the list populated and filter as you type in either Check# or E-mail

OR

I want a list populated by value on click.


HOW DO I
- call to the external workbook to populate the listbox when it opens? I need the first 3columns out of 7 and have the properties set in the UserForm (also image attached). When I got it to work it appeared beautifully. I had used a custom formula because it's a table that will grow as time passes:
=OFFSET('directorylocation\[DATABASE.xlsm]DATABASE'!$A$2,0,0,COUNTA('directorylocation\[DATABASE.xlsm]DATABASE'!$A:$A)-1,3)

- Get a click event to actually display results in the listbox?


The listbox is ONLY for display and there will be no editing done. Am I even using the right object? I feel really dumb here ?

 

[TechAgent007]

So, I realized I saved the Name Formula in the wrong Workbook. So I saved it there. I got it to open the file and return all the values on click event. But not the entered text...

However, the way I got it to access the workbook fully opened it. Which I want to avoid.

Open (not visible) > search > return results > close database.xlsm

I used Workbooks.Open "[PATH]" and then changed it as I don't want to actually visibly open. Now I don't think it is actually opening.



I changed the Name Formula thinking I wasn't calling the right sheet. And now I get this:

Is anyone able to help here?

I have reworked this to remove company/customer info.
I did use Option Explicit

Private Sub cmdSearch1_Click()
Dim RowNum As Long
Dim SearchRow As Long
Dim App As New Excel.Application
Dim wBook As Excel.Workbook
Dim FileName As String
RowNum = 2
SearchRow = 2
FileName = ThisWorkbook.Path & "\DATABASE.xlsm"
Set wBook = App.Workbooks.Open(FileName)

App.Visible = False


With wBook.Sheets("DATABASE")

Do Until Cells(RowNum, 1).Value = ""
    If InStr(1, Cells(RowNum, 1).Value, txtChecksearch.Value, vbTextCompare) > 0 Then
        Worksheets("Results").Cells(SearchRow, 1).Value = Cells(RowNum, 1).Value
        Worksheets("Results").Cells(SearchRow, 2).Value = Cells(RowNum, 2).Value
        Worksheets("Results").Cells(SearchRow, 3).Value = Cells(RowNum, 3).Value
        SearchRow = SearchRow + 1
    End If
    RowNum = RowNum + 1
Loop

If SearchRow = 2 Then
    MsgBox "No results with your criteria."
    Exit Sub
End If

lstSearchResults.RowSource = "SearchResults"

End With

wBook.Close Savechanges:=True

App.Quit

Set App = Nothing

Application.ScreenUpdating = True
End Sub

 

[jagrenet]

I am working on a very similar situation and like you, am waiting to see a solution. I am using an identical "If Instr " statement along with vbTextCompare to get my results. I am attempting to search an "external" spreadsheet and return the results to a ListBox, .... based on the user's Input to the TextBox.

It works very well, in fact, TOO well - LOL. It returns EVERY record rather than ONLY the Rows of the records that match the TextBox.Value. I am hoping somebody who sees this message can straighten it out.