Add the first code to the Workbook_Open event. You can set a password on the VBA Project to prevent users from viewing/amending the code.
If security is really important you should also add the Workbook_BeforeClose code at bottom to protect the workbook when it's closed. This will prevent the user from overriding the Workbook_Open code by disabling macros.
Private Sub Workbook_Open()
Dim ws As Worksheet
Dim startDate As Date, endDate As Date, varDays As Integer, varPassword As String
varPassword = "admin123"
startDate = DateValue("11 Jul 2011") '<----- SET START DATE
varDays = 30 '<----- SET NUMBER OF DAYS TILL PROTECTION
endDate = DateAdd("d", varDays, startDate)
If Date > endDate Then
For Each ws In ThisWorkbook.Worksheets
ws.Protect varPassword
Next ws
ActiveWorkbook.Protect varPassword, Structure:=True, Windows:=True
Else
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect varPassword
Next ws
ActiveWorkbook.Unprotect varPassword
End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
Dim varPassword As String
varPassword = "admin123"
For Each ws In ThisWorkbook.Worksheets
ws.Protect varPassword
Next ws
ActiveWorkbook.Protect varPassword, Structure:=True, Windows:=True
End Sub
Bookmarks