Excel VBA Userform to search multiple worksheets

[ExcelEndeavor]

First, some context...

I have a userform that users complete to capture the salesperson, product ID, product category, revenue amount, and sales date. There is a submit button that funnels the data into 1 of 5 seperate worksheets that are based on product category selected in the userform:

Industrial, consturction, maintenance, hospitality, administrative

(All of the worksheets are formatted exactly the same with the salesperson in column C - it's just to keep the categories separate)

*** This part works great ***



Now, I want to create a userform to search for the salesperson across all 5 worksheets and display the results into a listbox. Then, I can double-click one of the results to open the original userform that was completed for the details of that item and/or make edits if needed. Again, the worksheets are all formatted exactly the same, with the Salesperson in column C.

How would I accomplish this?

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Excel VBA Userform to search multiple worksheets
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[DanteAmor]

Hi @ExcelEndeavor

Check out what you think of my next proposal.
Not only are you going to load column C (salesperson) in the userform2 listbox, I propose to load more columns, so that you have more information in the listbox about each salesperson.
In my example, I'm loading 5 columns, A through E, but you can load less or more.

In addition to the 5 columns, in each of the rows of the listbox, I am loading the name of the sheet and the row number, so that when you double click on a item, you know in which sheet and in which row the item is located.

So, in this line you control from which column and to which column you want to load. Even from which row, in this example the data starts in row 2.

a = sh.Range("A2:E" & sh.Range("C" & Rows.Count).End(3).Row).Value

Then, on next line, you control which columns you want to display in the listbox. That is, they are loaded in the listbox, but you can decide which columns to hide.
In this example columns B, D and E are hidden, and that is because they have a column width of 0 (zero)

.ColumnWidths = "50;0;100;0;0;80;20"

The last 2 columns with widths 80 and 20 are to show the name of the sheet and the row number of the record.
Likewise, if you want to hide that data you can put something like this:

.ColumnWidths = "50;0;100;0;0;0;0"

Put the following code in userform2:

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Dim shName As String
  Dim nRow As Long
 
  With ListBox1
    shName = .List(.ListIndex, .ColumnCount - 2)
    nRow = .List(.ListIndex, .ColumnCount - 1)
    MsgBox "Sheet : " & shName & vbCr & "Row: " & nRow
    Unload Me
    UserForm1.Show
  End With
End Sub

Private Sub UserForm_Activate()
  Dim a As Variant
  Dim sh As Worksheet
  Dim arr As Variant, itm As Variant
  Dim i As Long, j As Long, k As Long
 
  arr = Array("Industrial", "construction", "maintenance", "hospitality", "administrative")
 
  k = 0
  For Each itm In arr
    Set sh = Sheets(itm)
    a = sh.Range("A2:E" & sh.Range("C" & Rows.Count).End(3).Row).Value
  
    With ListBox1
      .ColumnCount = UBound(a, 2) + 2
      .ColumnWidths = "50;0;100;0;0;80;20"
      For i = 1 To UBound(a, 1)
        .AddItem
        For j = 1 To UBound(a, 2)
          .List(k, j - 1) = a(i, j)
        Next
        .List(k, 5) = sh.Name
        .List(k, 6) = i + 1
        k = k + 1
      Next
    End With
  Next
End Sub

I suggest you play a little with these lines of the macro so you know how it works.

    a = sh.Range("A2:E" & sh.Range("C" & Rows.Count).End(3).Row).Value

      .ColumnWidths = "50;0;100;0;0;80;20"

Let me know any questions you have and I will be happy to help you.

 

[ExcelEndeavor]

Hi @ExcelEndeavor

Check out what you think of my next proposal.
Not only are you going to load column C (salesperson) in the userform2 listbox, I propose to load more columns, so that you have more information in the listbox about each salesperson.
In my example, I'm loading 5 columns, A through E, but you can load less or more.

In addition to the 5 columns, in each of the rows of the listbox, I am loading the name of the sheet and the row number, so that when you double click on a item, you know in which sheet and in which row the item is located.

So, in this line you control from which column and to which column you want to load. Even from which row, in this example the data starts in row 2.

a = sh.Range("A2:E" & sh.Range("C" & Rows.Count).End(3).Row).Value

Then, on next line, you control which columns you want to display in the listbox. That is, they are loaded in the listbox, but you can decide which columns to hide.
In this example columns B, D and E are hidden, and that is because they have a column width of 0 (zero)

.ColumnWidths = "50;0;100;0;0;80;20"

The last 2 columns with widths 80 and 20 are to show the name of the sheet and the row number of the record.
Likewise, if you want to hide that data you can put something like this:

.ColumnWidths = "50;0;100;0;0;0;0"

Put the following code in userform2:

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Dim shName As String
  Dim nRow As Long
 
  With ListBox1
    shName = .List(.ListIndex, .ColumnCount - 2)
    nRow = .List(.ListIndex, .ColumnCount - 1)
    MsgBox "Sheet : " & shName & vbCr & "Row: " & nRow
    Unload Me
    UserForm1.Show
  End With
End Sub

Private Sub UserForm_Activate()
  Dim a As Variant
  Dim sh As Worksheet
  Dim arr As Variant, itm As Variant
  Dim i As Long, j As Long, k As Long
 
  arr = Array("Industrial", "construction", "maintenance", "hospitality", "administrative")
 
  k = 0
  For Each itm In arr
    Set sh = Sheets(itm)
    a = sh.Range("A2:E" & sh.Range("C" & Rows.Count).End(3).Row).Value
 
    With ListBox1
      .ColumnCount = UBound(a, 2) + 2
      .ColumnWidths = "50;0;100;0;0;80;20"
      For i = 1 To UBound(a, 1)
        .AddItem
        For j = 1 To UBound(a, 2)
          .List(k, j - 1) = a(i, j)
        Next
        .List(k, 5) = sh.Name
        .List(k, 6) = i + 1
        k = k + 1
      Next
    End With
  Next
End Sub

I suggest you play a little with these lines of the macro so you know how it works.

    a = sh.Range("A2:E" & sh.Range("C" & Rows.Count).End(3).Row).Value

      .ColumnWidths = "50;0;100;0;0;80;20"

Let me know any questions you have and I will be happy to help you.

Thank you for your very thorough response! I plugged it in and the results come back blank. I'm not getting an error though so perhaps I am pointing it to the wrong location. This might be the issue = the upper portion of each worksheet has summary fields, so the data doesn't actually start until row 17 (row 16 is frozen with the headers). I edited your suggest code to reflect this:

a = sh.Range("A17:E" & sh.Range("C" & Rows.Count).End(3).Row).Value

Is there something else I should be changing as well?

 

[DanteAmor]

Do you have more data after row 17?
Do you have auto filters?
salesperson is in column "C", is column C populated?

Did you put the code in your auxiliary userform?
Are you running the auxiliary userform?

Can you put the real names of your main userform and the auxiliary userform and the listbox that is in the auxiliary userform?

How do you have this line:

.ColumnWidths = "50;0;100;0;0;80;20"

Only run the auxiliary userform, it should give you at least the name of the 5 sheets.

 

[ExcelEndeavor]

Do you have more data after row 17?
Do you have auto filters?
salesperson is in column "C", is column C populated?

Did you put the code in your auxiliary userform?
Are you running the auxiliary userform?

Can you put the real names of your main userform and the auxiliary userform and the listbox that is in the auxiliary userform?

How do you have this line:

.ColumnWidths = "50;0;100;0;0;80;20"

Only run the auxiliary userform, it should give you at least the name of the 5 sheets.

I have data starting in row 17 and continues to 116.
I don't have any autofilters
Salesperson is in column C and I manually popluated each worksheet to test the code

Sorry - I am not sure what is meant by "auxiliary userform"
I have 2 userforms =
The first one is "RequestForm" which is used to input the data in the first place
The one I am working in is "OpenExistingForm", which is the one I pasted your code into to find the data

I didn't change ColumnWidths because I wanted to see them first before adjusting the size

When I run the code, nothing shows in the list box. I'm not getting an error tho either.

Is there a setting I should changing in the Properties window?

 

[DanteAmor]

I don't know what problem you have.
You could share your file. If you have sensitive information, you can change it to generic data.

I share my file with you:

listbox find data.xlsm

drive.google.com

In the first "Industrial" sheet I put a button to open the userform, clic the button, there you can see how all the data from the 5 sheets is loaded, in ListBox1 of the OpenExistingForm.

Check how I have the code inside the "OpenExistingForm" userform and try to do it in your file.