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]

View attachment 7488

That is interesting. I never tried printing a Userform.
I guess using a Multicolumn Listbox versus a TextBox would depend on how you want things to look.

 

[Mel Smith]

I printed out a blank UserForm (with all the empty boxes) as previously posted and I was quite pleased with the format. at least it should keep the coaches and management happy!

 

[My Aswer Is This]

Assuming the TextBox named TextBox1 is the textbox you plan to enter the search value in and assuming we will be searching a sheet named "Me" Modify sheet name

Try this
We will work later on other sheets if this works.

Private Sub CommandButton1_Click()
'Modified 2/24/2020 3:30:39 PM EST
Dim ans As String
ans = TextBox1.Value
Dim dteRow As Variant
Dim i As Long
Dim Del As Variant
'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)
With Sheets("Me")
dteRow = Application.Match(ans, .Columns(1), 0)
If IsNumeric(dteRow) Then
For i = 2 To 26
Me.Controls("TextBox" & i).Value = .Cells(dteRow, Del(i - 1))
Next
Else
MsgBox ans & vbNewLine & "Notfound"
End If
End With
End Sub

 

[Mel Smith]

I changed the "Me" to "Championship Averages" and it worked just as I hoped! Now I'm going to sit and study the code to try to understand it.

Thank you.

 

[My Aswer Is This]

I try to write code that others can understand.

So if I understand it all sheets are exactly the same.
So if that is so then we could tell the script to work on sheet(1) to Sheets(8)

And so we will need 25 times 8 textboxes that makes TextBox 2 to TextBox 201 is that correct?

And they go in order from sheet 1 to sheet 8

That would be the easy way if that is correct.

The script does not need to know all the sheet names
The script just starts with Sheet(1) and goes to Sheet(8)

Sheet(1) is the sheet in the far left position on your Workbook

Will that work?

If not tell me from which sheet to which sheet.
This will be easy.

 

[Mel Smith]

I've looked at the output (to print) and have come to the conclusion that I ought to have two Userforms as there will be far too much to get on one page. The Userform that we are working on will have the first four sheets (sheets 1, 2, 3 and 5); Championship Averages (sheet 1), First Class Averages (combined)(sheet 2), 50 Over League Averages (sheet 3) and T20 Blast Averages (sheet 5). the TextBoxes number sequentially from 2 to 101.

I think then a duplicate UserForm, (UserForm2) for worksheets 6, 7, 8 and 9. Second XI Champ. Averages, Second XI 50 Over Fr. Averages, Second XI T20 Averages and Second XI Friendly Averages.

i hope that this makes sense.

Mel

 

[My Aswer Is This]

Hold on a little bit. I may have a solution where we do not need two Userforms.
I will get back with you in a hour.

 

[My Aswer Is This]

Here is something to consider.
Look in the Userform Tool Box and you will see Multipage.
Put a Multipage control on your Userform.

Then put half your TextBoxes on one Multipage1 page1 and the other half on Multipage1 Page2

If you notice a Multipage has pages just like a Workbook has sheets.

Now the Multipage1 Page that is visible when you click PrintForm only that page will print.

You can add more MultiPage1 pages if you want just like adding more Worksheets to a Workbook. But only in design mode.
Just right click on any Multipage1 page and choose New Page.

A Multipage is like having a new UserForm all inside the master UserForm

Try this on a copy of your workbook in case you do not like this and see what you think.
The only controls that will print are the ones visible when you click PrintForm.
So any thing you do not want to print just put them on A Multipage1 Page which is not visible.

 

[Mel Smith]

What a neat idea. I've had a look at it and played around but on reflection have gone with two UserForms, UserForm1 and UserForm2. UserForm1 retrieves data from worksheets 1, 2, 3 and 5. UserForm2 retrieves data from worksheets 6, 7, 8 and 10. The TextBoxes are numbers sequentially from 2 to 101. Do I need to rename/number the TextBoxes on UserForm2 to start at 102 up to 201?

Mel

 

[My Aswer Is This]

Not sure how much of this you have figured out on your own and what help you now need.
I assume now your saying you want to have 4 sheets on one Userform
And four on the other Userform
That means 100 textboxes on each UserForm.

If you were doing the programing you would know this gets more complicated when the user who is not writing the code wants things done differently from the one writing the code.

Like For sheet1 I have to tell the script to start at Textbox 2 but on all the other sheets I have I have to tell the script to start on TextBox1

So when I write the loop to do all four sheets it gets more complicated.
I asked that you use A TextBox Name answer for the TextBox Where you enter the Name to search for in column A and then start loading values into Textbox1 for sheet1 and then the script would know when we get to TextBox 26 we start getting values from sheet2 and then when we get to 46 we start getting values from sheet 3 and so on.
To modify a textbox name all you do is click on the textbox and in the properties window change the name. So why can you not change the name on a Textbox to Answer and make things easier for me.

As far as the other userform I would set it up exactly the same as the first one.
We will have run the same script on each userform so just use TextBox 1 to 100
Please give the Search for Textbox a name like "Answer" do not make it difficult by using Textbox1

Use the same name in both userforms so we can use the same exact code except for the sheets