Excel 2007 - Auto Populate fields based on Cell Entry

Attached is a spreadsheet with notes.

Selections made by Department Heads on this spreadsheet are used to setup an employee's access to server data.  I'd like to make their job easier by auto-populating checkboxes based on an employee's status.  

For instance, a Service Technician's access to data would be different than a Service Manager's access to data.  I'd like the Department Head to simply choose either the Service Technician checkbox or the Service Manager checkbox and have the proper checkboxes for access automatically get checked.

Employment categories are listed on the spreadsheet along with more notes concerning what I'm trying to accomplish.

I just don't know the best method to do so.  "IF" statements look like they'd get too complicated to use.

Please advise.Test.xlsx
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
Try attached
Accept macros on open.
Set a category and press the button "Set Check Boxes".
I have only set the values for Service Technician and Administration.

The definitions are on the sheet "CheckBox Assignment".
You can hide it.
The category names are in row 2 with the corresponding check box names in row 1.
And the access list in columns B:D, with the corresponding check box names in column A.
Set an "x" or something else if the check box must be used for the category.
Grey rows are the header lines without check box.
The cells are yellow, and turn green when you mark.
You can filter on colours, to make it easier to copy.
For Administration I have filtered on yellow, put an x in the first cell, and copied that down.

The access list on assignment sheet, is in the same order, and in the same rows as on the selection sheet TEST.
The checkbox names in column A and row 1 are generated by the macro MakeCheckBoxList.
Position is the row, so if the selection sheet setup is changed, a similar change must be done on the assignment sheet.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
baleman2Author Commented:
My Gosh - this is PERFECT!!!!!  I would never have thought of this.  Thanks so much!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.