password unlock for specific cells only

[FrankF1643]

I have a worksheet for which all cells are locked and password protected. I have a subset of users of this file who are allowed to make changes to only a few cells. I would like to be able to give them a second password that unlocks only those few cells as opposed to the password that unlocks the whole sheet. Is this possible.

 

[Snakehips]

Frank,
Some thoughts....
In say xl2007 >> Review tab >>Changes you will find an option to 'Allow users to edit ranges'

Using that you can select a range of cells you wish to be edited and set a password to permit.
Then you would need to protect the sheet but allowing 'Select locked cells'.
User attempting to edit such cells will be asked for PW. Editing other cells will be denied.
The problem is that once the cells are unlocked by the password they remain unlocked until your user re-protects.

Will your users be disciplined enough to re-protect after use?

If not then you will need to use some simple vba, either to control the whole thing or just to ensure that the cells are locked down again after use.
However, to do that you would need to be able to identify when to do it, what is the event to trigger it, or could provide a one click button for user.

Hope that helps.

 

[FrankF1643]

Thanks Tony I am not sure I trust them to re-protect. What would the code look like not very good with vba yet) I already have code in that sheet that unlocks the sheet does some filtering and then locks it again. What would I need to add to that? Private Sub Worksheet_Activate()
ThisWorkbook.Activate
Application.ScreenUpdating = False
Sheets("Profit Calc").Unprotect Password:="xxxx"
Application.EnableEvents = False
Dim lr As Long
lr = Cells(Rows.Count, "XEA").End(xlUp).Row
Range("$XEA$1:$XEA$" & lr).AutoFilter Field:=1, Criteria1:="1"
Sheets("Profit Calc").Protect Password:="xxxx"
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[FrankF1643]

Tony - the code I have looks like it already does the trick. It even relocks the cells with the separate password. Thanks for the help. Much easier than I thought it would be

 

[Snakehips]

Frank,

Am am off out shortly so cannot look at this for an hour or two.

Is there any event or anything identifiable that marks the end of the users session with these cells?
We could certainly code for a re-protect if they click outside of their allowed, un protected range i.e. on any of the still protected cells?
Or when sheet is activated?
??
??