Start Macro When Workbook Opens?

[smlaff01]

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

[Rick_Stanich]

In the VBE select "ThisWorkBook", right click and select "View Code", once open in the left side menu select "WorkBook" and the right side menu select "Open". Paste the following inside.

Please Login or Register  to view this content.

Now when you open the workbook the worksheet "Sheet1" (your sheet) will always be protected.