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]

Cross-posted here.

Please note the forum rule on cross-posting, and follow it in the future. Thanks.

Well that is sad to see. I was about to help here but I guess not now.

 

[Mel Smith]

It’s TextBox184

Mel

 

[My Aswer Is This]

In your original post you did not mention Tables but then later when you post your code its referring to tables.

So do we have 8 sheets each with a different table name.

I always start projects like this with just a few controls like Textbox's and then if I get what I want I continue on adding all my Textboxes and work from there.

It's always easier to loop through controls by their number. So keeping their name the same but just start with TextBox1 the default name and then TextBox2 and so on makes things a lot easier.

In my script I named a TextBox Answer and the script searches for the value entered in the Textbox named answer. Easier in my opinion then searching for the value entered in Textbox 169

And you can see from my script how I loop through 4 Textbox's with only a very few lines of code.

Maybe you might want to consider this approach. If you need more help let me know.
But it is hard to help when I see you have posted this same question on at least one other online Excel Help forum

Private Sub CommandButton1_Click()
'Modified 2/24/2020 11:02:52 AM EST
Dim sf As String
sf = Answer.Value
Dim dteRow As Variant
Dim i As Long
With Range("Table1[Name]")
dteRow = Application.Match(sf, .Columns(1), 0)
If IsNumeric(dteRow) Then
For i = 1 To 4
Me.Controls("TextBox" & i).Value = .Cells(dteRow, i)
Next
Else
MsgBox sf & vbNewLine & "Notfound"
End If
End With
End Sub

 

[Mel Smith]

I am suitably chastised and rebuked. It will not happen again!

Thank you for your guidance and suggestions and it seems clear to me that I must start this project again from scratch. I think that first of all I need to share a copy of my original workbook. What I am looking to achieve, using the UserForm I posted this morning, is to enter a player's name in the first text box and have every other (empty) box filled with data from the eight worksheets. Currently, these worksheets are not set out as tables although they are set out in exactly the same way, each row and column receiving data according the the match type. The worksheets are named: Championship Averages, First Class Averages (combined), One Day Cup Averages, T20 Blast Averages, Second XI Champ. Averages, Second XI Trophy Averages, Second XI T20 Averages, Second XI Friendly Averages and Second XI "Red Ball Cricket" Averages. I attach below (using XL2BB) an excerpt of the first worksheet. I am most grateful for your help.

Mel

2020 season averages.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X			AA	AB	AC	AD	AE	AF	AG
3		Pld	Inns	N.o	Runs	Highest	Ave	100s	50s	Balls	Rs/100	4s	6s	Cts	St'd	Batting index			Ovs	Mdns	Runs	Wkts	Ave	Best			St Rt	Rns/Ov	5w	10w	Wides	No Balls	Bowling index
4	TR Ambrose						~~~~											TR Ambrose	0.0			0	~~~~~				~~~~	~~~~~
5	L Banks						~~~~											L Banks	0.0			0	~~~~~				~~~~	~~~~~
6	IR Bell						~~~~											IR Bell	0.0			0	~~~~~				~~~~	~~~~~
7	HJH Brookes						~~~~											HJH Brookes	0.0			0	~~~~~				~~~~	~~~~~
8	EA Brookes						~~~~											EA Brookes	0.0			0	~~~~~				~~~~	~~~~~
9	MGK Burgess						~~~~											MGK Burgess	0.0			0	~~~~~				~~~~	~~~~~
Championship Averages

 

[My Aswer Is This]

I need to know in what column we will search for the value entered in the Textbox

And I think you should name the TextBox Answer or some such name you said the First Textbox.

How am I to know what is the first TextBox.

And why does the script you showed refer to Table names.

Lets start simple and load just the values from the first sheet and not try all 8 sheets at this time.
So give me just the name of the first sheet.
And how many columns in that sheet do we need to look at to load the Textboxs

So we will look at column A for the name then load the values in columns
B to Z or what ever you have into the Textboxs

The textboxes should Have the default names Like Textbox1 TextBox2 etc.

Then I tell the script to go from TextBox 1 to 9 or what ever that would be.

 

[Mel Smith]

The column to search on is column A (A4:A63).

I will start the UserForm from scratch and name the Textbox as you suggest. It will be the first textbox I put on the form. Please disregard the previous script as i was simply trying to get something to work.
The first sheet to search is named Championship Averages. The columns which have data to be searched and 'loaded' into the userform are:
B,C,D,E,F,G,H,I,J,K,L,M,N,O (top row on userform empty textboxes) and then: S,T,U,V,W,X, AA,AB,AC,AD,AE,AF.

The text boxes will be in numerical order starting at 2 to inclusive.

Mel

 

[My Aswer Is This]

I do have another question.

What do you plan to do with all this information once you see it on the userform?
Do you just want to look at it or what.

Because if that is all you want to do is look at it we could load all this data into one Multicolumn ListBox

 

[Mel Smith]

I plan to print it, probably to .pdf although maybe to paper, then repeat for each player. Would a Multicolumn ListBox print out in the same way?

 

[My Aswer Is This]

I plan to print it, probably to .pdf although maybe to paper, then repeat for each player. Would a Multicolumn ListBox print out in the same way?

I have never tried to print a userform.
Show me the script your using to Print the Userform

 

[Mel Smith]