Lock/Unlock Cells Based on User Type

[jzeusj]

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.

 

[Peter_SSs]

Welcome to the MrExcel board!

Here's something to try in a copy of your workbook, but note ..

1. You'll have to adjust the code to add an extra category (I only did Group A and Group B), match sheet name to yours etc.

2. It would easily be defeated by the user not enabling macros. If that is an issue, you need to search the forum for threads about forcing users to enable macros.

3. This allows 'Group' users to edit row 1 as well. That could be changed if you want.

4. The sheet remains unprotected if the admin pasword is provided so an admin has full access.

The code goes in the ThisWorkbook module.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()<br>    <SPAN style="color:#00007F">Dim</SPAN> pw <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Crit <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> bAdmin <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <br>    pw = Application.InputBox("Enter your password")<br>    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> pw<br>        <SPAN style="color:#00007F">Case</SPAN> "admin"    <SPAN style="color:#007F00">'<- pw for admin</SPAN><br>            bAdmin = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">Case</SPAN> "a"        <SPAN style="color:#007F00">'<- pw for Group A</SPAN><br>            Crit = "=Group A"   <SPAN style="color:#007F00">'<- Col C value for Group A</SPAN><br>        <SPAN style="color:#00007F">Case</SPAN> "b"        <SPAN style="color:#007F00">'<- pw for Group B</SPAN><br>            Crit = "=Group B"   <SPAN style="color:#007F00">'<- Col C value for Group B</SPAN><br>        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN><br>            <br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Data")             <SPAN style="color:#007F00">'<- Change to suit</SPAN><br>        .Unprotect Password:="Secret"<br>        .AutoFilterMode = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> .Cells<br>            .Locked = <SPAN style="color:#00007F">Not</SPAN> bAdmin<br>            <SPAN style="color:#00007F">If</SPAN> Crit <> "" <SPAN style="color:#00007F">Then</SPAN><br>                .AutoFilter Field:=3, Criteria1:=Crit<br>                .SpecialCells(xlCellTypeVisible).Locked = <SPAN style="color:#00007F">False</SPAN><br>                .AutoFilter<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> bAdmin <SPAN style="color:#00007F">Then</SPAN><br>            .Protect Password:="Secret", DrawingObjects:=True, Contents:=<SPAN style="color:#00007F">True</SPAN>, Scenarios:=True<br>            .EnableSelection = xlUnlockedCells<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = True<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[jzeusj]

First, thanks for the response and welcoming me to the board. I understand your points 1-4, but your solution was a bit different than I expected. I probably wasn't clear in my description, but I get a bit lost in the last half of your code. I don't see where we lock/unlock rows based on the contents of Column C. At the risk of sounding redundant, I'll try and restate my problem. I've also pasted some sample data below. If a user from Group A enters a Group A password, they should be allowed to only update Group A rows (row 3 in my sample data). Likewise, B and C groups can only update their respective rows as well. Admin access seems to be working just fine, but to be clear, I don't want any user from Groups A, B, or C to do anything except update their respective rows (they cannot add/delete rows or columns). Thanks again for your time/effort.

Excel Workbook
	A	B	C	D	E	F	G
1	Last Name	First Name MI	Group	Course 1		Course 2
2	Adams	John	C	2-Dec-10	2-Dec-11	2-Dec-10	2-Dec-11
3	McDonald	Ronald	A	*	31-Dec-00	*	31-Dec-00
4	Smith	Jane	C	28-Jun-11	28-Jun-12	28-Jun-11	28-Jun-12
5	Doe	John	B	10-Aug-11	10-Aug-12	13-Jan-11	13-Jan-12
Personnel

 

[Peter_SSs]

I didn't have your actual data before, but I think you may have just missed this part

Crit = "=Group A"   '<- Col C value for Group A

Your data now shows that the Group A value in column C is just "A" not "Group A" as I had interpreted from your first post. So try changing this line to

Crit = "=A"

and a similar change for Group B.

 

[jzeusj]

Ha, I'm sorry...usually not this dense.

I think I understand what's going on now. And yes, it appears this is going to work out quite nicely. Thanks so much!

 

[Peter_SSs]

.. it appears this is going to work out quite nicely. Thanks so much!

Great.

Just another thought that you may want to consider. If you remove or comment out the red line, then when, say, a Group B user is in the sheet, it will be filtered so they can only see 'B' rows.

If Crit <> "" Then
    .AutoFilter Field:=3, Criteria1:=Crit
    .SpecialCells(xlCellTypeVisible).Locked = False
    .AutoFilter
End If

 

[jzeusj]

Nice, I do like filtering out the extra data.

So, I somewhat simplified my problem when I explained it. In reality, there are 3 groups, but group 1 is comprised of A and B personnel, group 2 is C and D personnel, and group 3 is E and F personnel. I was just trying to simplify it to its core problem.

Now, I'm working on how to translate Crit = "=A" to Crit = "=A" or "=B", etc. ...but I can work on that. You've been awesome. Thanks!

 

[Peter_SSs]

If you need some more help with that, please detail a bit more about how people will log in and what they should be able to access etc.

For example if 'D' person from group 2 opens the sheet what would they enter for a password (doesn't need to be the real one for this exercise) and what should they be able to access?

How would this differ from a group 2 'C' user or a group 3 'F' user etc

 

[jzeusj]

We will still have just 3 logins, but would be broken up like this: "Password AB", "Password CD", etc. So, if someone enters "Password AB", they can update rows with either "A" or "B" in Column C. Correct me if I'm wrong, but it appears I'll need to change Crit to an array. When filtering, it would now be Criteria1:=Array(Crit) or something along those lines. Crit will now mean "A" or "B" instead of just "A".

 

[Peter_SSs]

If there is just 2 criteria per group, try this sort of structure.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()<br>    <SPAN style="color:#00007F">Dim</SPAN> pw <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Crit1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Crit2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> bAdmin <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <br>    pw = Application.InputBox("Enter your password")<br>    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> pw<br>        <SPAN style="color:#00007F">Case</SPAN> "admin"<br>            bAdmin = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">Case</SPAN> "ab"<br>            Crit1 = "=A"<br>            Crit2 = "=B"<br>        <SPAN style="color:#00007F">Case</SPAN> "cd"<br>            Crit1 = "=C"<br>            Crit2 = "=D"<br>        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN><br>            <br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Data")<br>        .Unprotect Password:="Secret"<br>        .AutoFilterMode = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> .Cells<br>            .Locked = <SPAN style="color:#00007F">Not</SPAN> bAdmin<br>            <SPAN style="color:#00007F">If</SPAN> Crit1 <> "" <SPAN style="color:#00007F">Then</SPAN><br>                .AutoFilter Field:=3, Criteria1:=Crit1, _<br>                    Operator:=xlOr, Criteria2:=Crit2<br>                .SpecialCells(xlCellTypeVisible).Locked = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#007F00">'                .AutoFilter</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> bAdmin <SPAN style="color:#00007F">Then</SPAN><br>            .Protect Password:="Secret", DrawingObjects:=True, Contents:=<SPAN style="color:#00007F">True</SPAN>, Scenarios:=True<br>            .EnableSelection = xlUnlockedCells<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = True<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>