Hi guys, I am in the middle of trying to get a piece of code to work. Through lots of help from the great members of the site and a little tweaking of my own I have developed some code that will track who makes changes to cells within a specified range and at what time those changes were made. I make use of an input box to record the users name and to unlock the sheet with a password (thanks Rick!).


The problem I have run into is that if I am in the sheet making edits, save/close the sheet,and then reopen it-the sheet is no longer protected. Also if I send the sheet to other users it is not protected.

If I open my VBA editor and click the play button to run my code, then everything works fantastic and the sheet is locked until I enter my name/password.

How can I make the sheet be protected everytime that it is opened? I guess basically I need this code to run when the sheet opens, instead of having to run it manually?

Here is my code so far:

Private Sub Worksheet_Protect()
ActiveSheet.Protect
End Sub

Private Sub CommandButton1_Click()
Dim slogin As String
Dim spassword As String
slogin = InputBox("Enter full name here!")
If slogin = "" Then
MsgBox "You must enter your full name!"
slogin = InputBox("Enter full name here!")
End If
spassword = InputBox("Enter password here!")
If spassword = "" Then
MsgBox "You must enter the password!"
spassword = InputBox("Enter password here!")
End If
If spassword = "brownforman" Then ActiveSheet.Unprotect
'Begin Registry setting
'"appname:=" = Desired name you create, variable or string, preferred a string
'"section:=" = Desired name you create, variable or string
'"Key:=" = Desired name you create, variable or string
'setting:=" = variable or string
SaveSetting appname:="AppNameHere", section:="SectionNameHere", _
Key:="Login", setting:=slogin
'End registry setting
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim V As Long
Dim sUser As String
Application.EnableEvents = False

'Begin Registry setting
'"appname:=" = Desired name you create, variable or "string", preferred a "string"
'"section:=" = Desired name you create, variable or "string"
'"Key:=" = Desired name you create, variable or "string"
'Notes: Variables do not use quotes, strings use quotes
slogin = GetSetting(appname:="AppNameHere", section:="SectionNameHere", _
Key:="Login")
'End registry setting

Set rng1 = Application.Union(Range("a1:g1"), Range("H:iv"))
Set rng = Application.Intersect(Target, rng1)
If Not rng Is Nothing Then Exit Sub

V = Target.Offset(0, 12).Value
If Target.Offset(0, 12) = "" Then

With Range("H" & Target.Row)
.Value = Target.Address & ": first entry by " & slogin & " at " _
& Now()
.ColumnWidth = 60
.Interior.ColorIndex = 33
End With
Target.Offset(0, 12).Value = Target.Value
Application.EnableEvents = True
Exit Sub
End If
Target.Offset(0, 12).Value = Target.Value
With Range("H" & Target.Row)
.Value = Target.Address & " changed from " & V & " to " & Target.Value & " by " _
& slogin & " at " & Now()
.ColumnWidth = 60
.Interior.Color = vbYellow
End With
Application.EnableEvents = True
End Sub