Excel protection options - when different areas need to be updated by different staff

Question

When using password protection in Excel 7 - what options do you have that don't require you to re-enter the password again before saving/closing the file - ie to keep it protected if you happen to forget to re enter the password when closing.

I usually use the Save as - General Options process - as you do not need to reenter the password before you close/save however I need to include a range of cells that another person can change (without having access to everything) as well as having the overall spreadsheet to remain as read only for other staff.

So I need 3 access levels - 2 with passwords and a 3rd as read only.

Answer 1

It is not possible to do this in one excel workbook. You should try to give different workbooks to different staffs and then have them point to each other to fill in the missing data.

Answer 2

If you don't want to use macros, there isn't a whole lot you can do. The General Options process is the only way that you don't need to re-enter a password before saving. If you have a separate worksheet tab for each user that can input, then you have can have a separate password by protecting at the sheet level but you will need to remember to re-protect the worksheet.

With macros, more things become possible. Here is how I would set it up.

1. Save your workbook as a macro file (.xlsm).
2. Create three Named Ranges, one that includes all the cells user 1 can change, one with all the cells user 2 can change, and one with all cells (user 1 & user 2) that can be changed. Make sure the named ranges are set at Workbook level, not worksheet level.
3. Switch over to the Visual Basic editor.
4. Add the below code to your workbook. The first piece goes in ThisWorkbook, the second & third pieces go into regular code modules. (To add a module, click Insert -> Module.)
5. Modify the constants in the code module to match the Passwords and Named Ranges you want.
6. Since the passwords are plain text in the macro, you need to lock the VBA Project so no one can view it. Click Tools -> VBAProject Properties... then select the Protection tab. Check Lock project for viewing and enter a password. (I don't generally bother to do this. Most of my users are not tech savvy enough to find the passwords and I'm only trying to protect from accidental or unthinking damage anyway.)
7. Save the changed workbook.

ThisWorkbook:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   LockUser GetUserInputRange(AllInputCells:=True)
End Sub

Private Sub Workbook_Open()
   UnlockInputCells
End Sub

Code Module1:

Option Explicit

Public Const PWD_REAL As String = "test"    ' this is the password the worksheet is actually locked with
Public Const PWD_INPUT1 As String = "test1" ' password to unlock NAMED_RANGE1
Public Const PWD_INPUT2 As String = "test2" ' password to unlock NAMED_RANGE2

Public Const NAMED_RANGE1 As String = "Name1"  ' Named Range for user 1
Public Const NAMED_RANGE2 As String = "Name2"  ' Named Range for user 2
Public Const NAMED_ALL As String = "AllCells"  ' Named Range that includes all input cells

Code Module2:

Option Explicit

Sub UnlockInputCells()
   UnlockUser GetUserInputRange
End Sub
Sub LockInputCells()
   LockUser GetUserInputRange
End Sub

'-----------------------------------------------
Function GetUserInputRange(Optional AllInputCells As Boolean = False) As Range
   Dim rng As Range, strInputMsg As String

   Set rng = Nothing
   strInputMsg = "Enter your password to edit." & vbCrLf & vbCrLf _
               & "Press cancel if you just want to look at the report."

   If AllInputCells Then
      Set rng = ThisWorkbook.Names(NAMED_ALL).RefersToRange
   Else
      Select Case InputBox(strInputMsg)
      Case PWD_INPUT1
         Set rng = ThisWorkbook.Names(NAMED_RANGE1).RefersToRange
      Case PWD_INPUT2
         Set rng = ThisWorkbook.Names(NAMED_RANGE2).RefersToRange
      Case PWD_REAL
         Set rng = ThisWorkbook.Names(NAMED_ALL).RefersToRange
      End Select
   End If

   Set GetUserInputRange = rng

End Function

Private Sub UnlockUser(rngInput As Range)
   Dim sht As Worksheet

   If Not rngInput Is Nothing Then

      ' unprotect the worksheet
      Set sht = rngInput.Parent
      sht.Unprotect PWD_REAL

      ' unlock given user input cells
      With rngInput
         .Locked = False
         .Interior.Color = XlRgbColor.rgbAliceBlue
         .Range("A1").Select
      End With

      ' reprotect the worksheet
      sht.Protect PWD_REAL
      MsgBox "Your input cells have been unlocked."

   End If

End Sub

Sub LockUser(rngInput As Range)
   Dim sht As Worksheet

   If Not rngInput Is Nothing Then
      ' If the range includes locked and unlocked cells, .Locked returns Null
      If Not rngInput.Locked Or IsNull(rngInput.Locked) Then

         ' unprotect worksheet
         Set sht = rngInput.Parent
         sht.Unprotect PWD_REAL

         ' lock given user fields
         With rngInput
            .Locked = True
            .Interior.ColorIndex = xlColorIndexNone
         End With

         ' reprotect worksheet
         sht.Protect PWD_REAL

      End If
   End If

End Sub

This code works as follows: When the workbook opens it will ask for a password. If the given password matches one of the known passwords, it will unlock the named range that goes with that password. If the user didn't unlock on open, they can unlock at any time by running the UnlockInputCells macro. Before the workbook saves, it will auto-lock all user input cells.