How to I restrict selection through based on a condition and display popup message for an error

I am trying to create a sub-list from a master list based on checkbox checked by user in excel. I have created Form check box in macro and used a formula to create the list and I am unable to us validation on it.

I have 2 column, first column contain names and 2nd column contains 3 different categories and a checkbox to select the names. A total of 220 names are there spread between these 3 categories

For Ex:- Excel Columns looks something like this seperated by |,

ABC|Batsmen|checkbox

XYZ|Batsmen|checkbox

PQR|Bowler|checkbox

EST|WK|Checkbox

checkbox mentioned here is Form Control checkbox from excel. Once these checkbox is selected, the names and 2nd column should be copied in a different column and the list should be concatenating the new selections. I will select 11 out of 220 names. Now these names have restrictions, I cannot choose more than 4 Batsmen, 3 bowlers and 1 WK and If I have already selected 4 batsmen in my list, checkbox should not allow me to add more batsman and pop up an error message.

I tried using - Conditional Formatting, on click event macro but both this approach works only for manual entry and not through selection made through checkbox.