User form to query (up to) 8 worksheets

[Mel Smith]

I have a workbook containing 8 identical sets of data, all laid out with the same persons occupying the same row and the same data occupying the same columns.

My question is, is it possible for a userform to ‘pull’ data from all 8 sheets? What I’m trying to achieve is to search on one name and the userform to then populate with all 8 sets of data in respect of that person.

Thanks for your help.

Mel

 

[My Aswer Is This]

And if I remember correctly you said all 8 sheets were exactly the same.

So when we start the script we do a search for the Name entered in the TexBox named Answer.
And search column A for that name.
So if you entered "Alpha" and we found Alpha in row 5 will we also know that Alpha will be in row 5 of all 8 sheets.

 

[My Aswer Is This]

Another thing.
It's best to have both Userforms exactly the same as much a possible.
I do know the labels you have above each Textbox will be different.

To make a copy of your userform do this.

Open your workbook
Look in the properties window and you will see your userform.

Open up another empty workbook.

In the properties window drag your userform into the New Workbook.

Rename the Userform in the New Workbook.

And then Drag that Userform back into the original Workbook
.

 

[Mel Smith]

I have (re)named TextBox1 on both UserForm 1 and 2 to "Answer" as you suggest. Sheet 1 values start at TextBox2, sheet 2 values at TextBox27, sheet 3 at TextBox52 and sheet 4 at Textbox77.

Your help really is much appreciated.

Mel

 

[Mel Smith]

I have copied UserForm1 in the manner you described and dragged it back as UserForm2. They are identical, apart from some of the labels.

Mel

 

[My Aswer Is This]

This is the script for Userform1
This script assumes you have 100 Textboxes on your Userform numbered 1 to 100
Like Textbox1 TextBox2

These are default names
And you need a Textbox named Search which is where you enter the search value.
And we look in column A of all the sheets for the value entered in the Textbox named Search.
And this script searches sheets 1 2 3 and 5

Private Sub CommandButton1_Click()
'Modified  2/26/2020  3:03:52 AM  EST
Dim ans As String
ans = Search.Value
Dim dteRow As Variant
Dim i As Long
Dim nn As Long
Dim Del As Variant
Dim sn As Variant
'These are sheet numbers
sn = Array(1, 2, 3, 5)
'These are the column Numbers shown below in the array
Del = Array(2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 19, 20, 21, 22, 23, 24, 27, 28, 29, 30, 31, 32)
    nn = 1
    For b = 1 To 4
        With Sheets(sn(b - 1))
            dteRow = Application.Match(ans, .Columns(1), 0)
                If IsNumeric(dteRow) Then
           
                    For i = 1 To 25
                        Me.Controls("TextBox" & nn).Value = .Cells(dteRow, Del(i - 1))
                        nn = nn + 1
                    Next
                Else
                    MsgBox ans & vbNewLine & "Notfound"
                End If
End With
i = 1
Next
End Sub

 

[Mel Smith]

Many, many thanks for this. So, just to be certain, I need to rename the original TextBox1 that I subsequently called "Answer" to "Search" and then ensure that the 100 TextBoxes are numbered sequentially started at 1 to 100.
Mel

 

[My Aswer Is This]

I guess in a previous post I may had said Answer and now I'm saying Search.
My most recent post I used Search.
So you need a TextBox Named Search someplace on your Userform
Enter a name in the Textbox named search and then click your button with the code and the script should run. Be sure and have 100 Textboxes named TextBox1 to Textbox100 or you will get a error.

 

[My Aswer Is This]

If you understand selecting and dragging. To create 100 TextBoxes quickly you can:
Create the first 25

Then with your cursor select all 25 and then press control while dragging and you can create 25 more which will now have new numbers

If the ones you just copied were numbered 1 to 25 the new ones will be numbered 26 to 50
Just a little tip if you want.

 

[Mel Smith]

I pasted in your code ran it. The first 75 boxes received data and then I got this error message. The M was highlighted in yellow.e.Controls("TextBox" & nn).Value = .Cells(dteRow, Del(i - 1))

For i = 1 To 25
Me.Controls("TextBox" & nn).Value = .Cells(dteRow, Del(i - 1))
nn = nn + 1

Mel

 

[My Aswer Is This]

I pasted in your code ran it. The first 75 boxes received data and then I got this error message. The M was highlighted in yellow.e.Controls("TextBox" & nn).Value = .Cells(dteRow, Del(i - 1))

For i = 1 To 25
Me.Controls("TextBox" & nn).Value = .Cells(dteRow, Del(i - 1))
nn = nn + 1

Mel

I suspect you do not have a textbox named Textbox1 or Textbox2 or something like that.
You do know you must have 100 textboxes named TextBox1 to TextBox100

To do a Test look at the code and where you see:
For b = 1 To 4
Change that to:
For b = 1 To 1

This way it will only do the first 25 Textboxes
If that works then change it to
For b = 1 To 2
And try again this time it will only do the first 50 boxes
And this may help you find the problem.