Userform - dependent combobox to select worksheet to return search results and data change

Dear community,

I have been reviewing the internet to identify if my objective within a VBA user form has already been undertaken but I can’t seem to hit the nail on the head and get a correct match. Results that I seem to find is a combobox confined to searching ranges within one worksheet and not more than one worksheet.

The requirement

Using an example template spreadsheet (attached to this post) I have 3 tabs which are named as School 1, School 2 and School 3. Each of these tabs collates pupil reference numbers, names, ages and expected grade. (I also have a tab which has a version tracker which will need to be retained in spreadsheet but is not required to be included in the requested userform)

I have set up a userform (PupilUserForm) which you can access via the VBA developer function. What I would like this userform to do is as follows:

1. Select the combobox which determines selects the worksheet to start the search function (i.e. School 1, School 2 and School 3)
   
2. Determining the selected ‘school x’ worksheet from the combo box, this will automate a mini listbox which will show all pupil reference numbers and associated pupil names from that chosen worksheet (range won’t be different in either of the three tabs, except the amount of rows of information according to number of pupils)
   
3. In addition, I would like to click on any of the pupils in the listbox and associated information will appear in the designated textboxes for age and expected grade
   
4. To add the icing to this VBA function cake, I would like to place some command buttons within this userform which will enable myself to update the values within text boxes (age and expected grade) and click ‘update’ or ‘clear’

Template attached

The spreadsheet template is attached and although I have tried to commence producing the VBA code for my request, I deem my unsuccessful production of a specified VBA would only confuse matters and therefore a blank template request I deem is the correct way for a resolution.

I hope this is clear and if there are queries, I hope to provide responses at the earliest possible opportunity.

Cheers!