I am creating a spreadsheet with multipl worksheets (12 to be exact, one for each month). On the 7th of the month after the worksheet locks for user editing. (2/7/2011 the January worksheet was locked). I do this by protecting all the sheets before close and then at open call for protectsheet to do a date check and unprotect the necessary tables. In the unprotected sheets I need to lock columns E, H, K and L. I am wondering is I should change a code or add something to do this and I can't figure out what to do. Any help would be greatly appreciated.
Workbook code currently being used:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Jan").Protect Password:="Secret"
Sheets("Feb").Protect Password:="Secret"
Sheets("Mar").Protect Password:="Secret"
Sheets("Apr").Protect Password:="Secret"
Sheets("May").Protect Password:="Secret"
Sheets("Jun").Protect Password:="Secret"
Sheets("Jul").Protect Password:="Secret"
Sheets("Aug").Protect Password:="Secret"
Sheets("Sep").Protect Password:="Secret"
Sheets("Oct").Protect Password:="Secret"
Sheets("Nov").Protect Password:="Secret"
Sheets("Dec").Protect Password:="Secret"
End Sub
Private Sub Workbook_Open()
Call ProtectSheet
End Sub
Sub ProtectSheet()
If Now() < DateSerial(2011, 2, 7) Then
Sheets("Jan").Unprotect Password:="Secret"
End If
If Now() < DateSerial(2011, 3, 7) Then
Sheets("Feb").Unprotect Password:="Secret"
End If
If Now() < DateSerial(2011, 4, 7) Then
Sheets("Mar").Unprotect Password:="Secret"
End If
If Now() < DateSerial(2011, 5, 7) Then
Sheets("Apr").Unprotect Password:="Secret"
End If
If Now() < DateSerial(2011, 6, 7) Then
Sheets("May").Unprotect Password:="Secret"
End If
If Now() < DateSerial(2011, 7, 7) Then
Sheets("Jun").Unprotect Password:="Secret"
End If
If Now() < DateSerial(2011, 8, 7) Then
Sheets("Jul").Unprotect Password:="Secret"
End If
If Now() < DateSerial(2011, 9, 7) Then
Sheets("Aug").Unprotect Password:="Secret"
End If
If Now() < DateSerial(2011, 10, 7) Then
Sheets("Sep").Unprotect Password:="Secret"
End If
If Now() < DateSerial(2011, 11, 7) Then
Sheets("Oct").Unprotect Password:="Secret"
End If
If Now() < DateSerial(2011, 12, 7) Then
Sheets("Nov").Unprotect Password:="Secret"
End If
If Now() < DateSerial(2012, 1, 7) Then
Sheets("Dec").Unprotect Password:="Secret"
End If
End Sub
Bookmarks