Thread: Lock/Unlock Cells Based on User Type

I've been wrestling with the best way to do this for awhile. Requirements:

1) I have a single spreadsheet with existing data (one row per person) that needs to be updated regularly.
2) There are 4 different types of users: Group A, Group B, Group C, and Admin.
3) Column C in each row identifies if it is data for a Group A person, Group B, or Group C.
4) Group A people should only be able to update Group A data, Group B only Group B data, etc.
5) Only the Admin should be able to add/delete rows (i.e. add or delete people from groups).
6) Admin should be able to update any row.

I looked into using "Allow users to edit ranges", but the problem is the data is pretty dynamic (rows are added/deleted regularly), so it seemed I would be constantly redefining the ranges.
I also looked into "Protect Sheet" for the admin piece. The problem is even though I only select "Insert rows" and "Delete rows" from the "Protect Sheet" menu, it seems to lock down every cell where I can't input data.

A possible (although clunky IMO) solution would be to test for the contents of Column C for the respective row every time somebody tries to change a cell. Then, based on that, prompt for a password. For example, I'm trying to enter data on a Group A row. I would be prompted for a password, and would need to enter "Group A password" into the dialog box for that cell to be unlocked. I would think a more elegant solution would be to ask for a password when opening the spreadsheet. Based upon the password (4 options: Groups A-C & Admin), grant the appropriate level of access.

Any ideas on how to do this? I'm open to any solution.

Thanks for your time.
I have posted this on 2 other forums with no answer yet:
http://www.ozgrid.com/forum/showthread.php?t=157371
http://www.mrexcel.com/forum/showthread.php?t=573265

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

Right, i've knocked a sample up for you, the password for the sheet is password, the passwords for the groups are "GroupA", "GroupB", "GroupC" and "Admin", in the sample the password box is called on open, if you "x" out of the password box the file closes, using the passwords only allows the user to edit the particular rows that contain their group, everything is reset on exit and the workbook saved.

Originally Posted by JBeaucaire

As per the Forum Rules, crossposting should include a link here to your other threads, to avoid people wasting their efforts on already tried suggestions posted elsewhere.

Please provide the link.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

JB, in his first post he did supply his cross post links

Originally Posted by Simon Lloyd

JB, in his first post he did supply his cross post links

(shame) (blush) My bad.