unlocking with username and autolocking when save

Hi all,

I found this code on the forum posted yesterday by another user. the post is as follows

Excel can automatically recognise individuals by their windows login name so you can fully automate the protect/unprotect of the correct sheets without even individuals having to use passwords.

The first step though would be to gather the id's of all users.

1. Create an additional sheet and call it UserLog
2. Put the following code into all the worksheet code modules

Private Sub Worksheet_Change(ByVal Target As Range)
Dim EndRow As Long
    If Target.Cells.Count > 1 Then Exit Sub
    EndRow = Sheets("UserLog").Cells(Rows.Count, "A").End(xlUp).Row + 1
    Sheets("UserLog").Range("A" & EndRow).Value = ActiveSheet.Name
    Sheets("UserLog").Range("B" & EndRow).Value = Environ("UserName")
End Sub

Once you know the windows login names and which sheet they are responsible for you can add the following code to each worksheet

Private Sub Worksheet_Activate()
    If Environ("Username") = "Andy" Then
        ActiveSheet.Unprotect Password:="mypass"
    Else
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True, Password:="mypass"
    End If

End SubLast edited by AndyLitch; Yesterday at 01:16 AM.

I have tried as per below and it is not working.

I have about 50 user that uses one document and will record their comments to instructions, but only 20 users will be able to add the instructions. The document needs to autolock when it is saved and can only be unlock with the username of the 20 users.

Environ Username refers to your Windows account name.