Active X Check Box to call up a UserForm

[jammoca]

Hello to all,

there is likely to be a better way to do this, so please, if there is, please let me know.

In the range ... Entry!A4:D300, I have ....

Student ID numbers in Column A
Student Names in Column B
Subject Class Codes in Column C
Subject Teacher Codes in Column D

eg:

80456	Abbas, Kasey	12MAB101	DBRAN
46662	Balmer, Ashleigh	12MAB101	DBRAN
82672	Bishop, Hamish	12MAB101	DBRAN
69626	Bridge, Liam	12MAB101	DBRAN
46283	Bultitude, Hamish	12MAB101	DBRAN
61608	Bester, Matthew	12MAB102	MCUJE
71059	Bowden, Alice	12MAB102	MCUJE
75001	Colbrook, Rowena	12MAB102	MCUJE
70529	Dunn, Daniel	12MAB102	MCUJE
74536	Fraser, Joshua	12MAB102	MCUJE
78312	Keech, Jackson	12MAB102	MCUJE
44087	King, Patrick	12MAB102	MCUJE
81620	Curtis, Brooke	12MAB301	TFLET
55922	Dwyer, Rupert	12MAB301	TFLET
62731	Fernando, Natalie	12MAB301	TFLET
80331	Furrer, Isabelle	12MAB301	TFLET
75653	Harper, Pat	12MAB301	TFLET
72991	Kennedy, Maddie	12MAB301	TFLET
80970	Liao, Andy	12MAB301	TFLET
82123	Chang, Matthew	12MAB302	DBRAN
70047	Chu, Matthew	12MAB302	DBRAN
47549	Cunningham, Charlie	12MAB302	DBRAN
69652	Dagg, Jackson	12MAB302	DBRAN
72841	Deeb, Sarah	12MAB302	DBRAN
83732	Deleau, Noémie	12MAB302	DBRAN
51625	Edwards, Savannah	12MAB302	DBRAN
70509	Ferres, Kirsten	12MAB302	DBRAN

<tbody>
</tbody>

On a different sheet, I have a series of Math subject class codes in the range ... PrintTemplate!Q4:T13 ... many cells have no subject in them ...

12MAB101	12MAB302
12MAB102
12MAB301	12MAB502	12MAB506
12MAB501
12MAB504

<tbody>
</tbody>

I've positioned an Active X Check Box beside (to the right of) each subject class code (including the cells that are empty) ... these check boxes currently have no code in them .. so the checkbox to the right of the subject class found in PrintTemplate!Q4 refers to the subject class found in PrintTemplate!Q4.

A user is likely to check more than one check box.

When a checkbox is clicked, I'd like the list of students who belong to that class to be displayed in a floating UserForm (perhaps named after the Class it refers to), AND beside each student name in that UserForm, I'd like another checkBox to appear, that if checked will cause three (3) things to happen ... 1) the name of the student the CheckBox refers to should appear in cell ... Print Template!B2 ... 2) the student number of the student the CheckBox refers to should appear in cell ... Print Template!B1 .... and 3) the range ... Print Template!B4:M26 ... should be printed on a single A4 piece of paper.

As I said, a teacher is likely to check more than one CheckBox, so the above three things (culminating in the printing of that cell range) will need to happen for each student chosen, so I'm guessing, once one student has been printed, the three steps need to happen for the next student chosen, and so on, and so forth.

Sometimes, a teacher might want to print the entire class list, and not just choose a few students to print, so perhaps, above the first student in the list (in the floating UserForm) should be the words 'PRINT ALL STUDENTS' with a checkbox beside it, so if checked, would print all the students in the list below it.

I realise this is a huge ask, and if there's a better way to do it, please let me know, but at least this strategy I can comprehend.

Is someone able to help me with the coding of ...

* the CheckBoxes referring to Subject Class Codes in the range ... PrintTemplate!Q4:T13 .. which cause a floating UserForm to appear with the student names for each Subject Class Code chosen
* the CheckBoxes (on the floating UserForm) that refer to each student in the displayed list, that when checked, will do those three (3) things mentioned
* the single CheckBox that will cause the printing for each student in that Subject Class List.

A huge thankyou if anyone can pull this off.

Kindest regards,

Chris Jamieson

 

[My Aswer Is This]

Doing things like this in my opinion is not the best way to do things:

"I've positioned an Active X Check Box beside (to the right of) each subject class code (including the cells that are empty) ... these check boxes currently have no code in them .. so the checkbox to the right of the subject class found in PrintTemplate!Q4 refers to the subject class found in PrintTemplate!Q4."


Just placing all these checkboxes looks like a lot of work to me unless you have some script doing this.

Why not put a "X" in the column next to your cell and then we could write code to look for a "X" in that column.




And trying to do this would take all sorts of coding to actually create a Useform Checkbox.

"AND beside each student name in that UserForm, I'd like another checkBox to appear"


I think it would be better for you to explain what your wanting and let us suggest how to do this.

And I would attempt to do some of this one step at a time till you see what can be done.

Including code to print all the sheets for you would be another task requiring more code.

Using hundreds of check boxes on your sheet and hundreds of check boxes on a userform is not in my opinion the way to do things

And code would have to be written to place these check boxes in the exact proper location and would require resizing the Userform to fit all these check boxes. And on and on.