Protecting workbook on open

  • Thread starter Thread starter Richard Lundgren
  • Start date Start date
R

Richard Lundgren

I have an excel workbook with multiple sheets which are
accessed from a custom menu. What I would like to do is
to be able to lock the workbook (or set the open property
to readonly) for users who are not authorised, and for
those who are to have the workbook as normal read / write
access.

The usernames are being picked up from the domain so I
have those ok, just cant quite figure out the rest.

Help much appreciated. thanks

Richard
 
You need to keep the sheets protected and only unprotect for certai
users. Setting to protect on open will fail if the user disable
macros. You also need to cover for users forgetting to re-protecto
closing. You need to password protect the code module so the shee
password is kept hidden.

Put these macros in the ThisWorkbook module.

'----------------------------------------------------
Private Sub Workbook_Open()
Dim MyUserName As String
MyUserName = UCase(Environ("username"))
If MyUserName = "USER1" Or MyUserName = "USER2" Then
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect ("MyPassword")
Next
MsgBox ("Unprotected")
End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
For Each ws In ThisWorkbook.Worksheets
ws.Protect ("MyPassword")
Next
ThisWorkbook.Save
MsgBox ("Protected and saved.")
End Sub
'-------------------------------------------------------
 
Back
Top