Decide when you want to make the check for the date 6 days. Two logical
choices would seem to be the
Workbook_Open()
or the
Workbook_BeforeClose()
events.
Once you make that decision, simply 'wrap' your existing Select Case block
in a loop that tests each worksheet.
The Workbook_Open() event would look something like this
Private Sub Workbook_Open()
Dim s_worksheet As Worksheet
For Each s_worksheet In ThisWorkbook.Workbooks
Select Case s_worksheet.Name
Case Is = "TS1", "TS2", "TS3"
If (DateDiff("d", s_worksheet.Range("B12"), Date) 6) Then
s_worksheet.Unprotect "admin"
s_worksheet.Range("C6:C12,D6
12,F6:F12,G6:G10,H6: H10,I6:I10").Locked = True
s_worksheet.Protect "admin"
End If
End Select
Next
End Sub
"Thu Ng" wrote:
Hi everyone,
I have 26 fortnightly timesheets in a workbook for every staff and would
like to lock each timesheet after some days.Can you please help me to fix my
code as the follows:
Select Case s_worksheet
Case "TS1", "TS2", "TS3"
If (DateDiff("d", B12, Date) 6) Then
s_worksheet.Unprotect "admin"
s_worksheet.Range("C6:C12,D612,F6:F12,G6:G10,H6: H10,I6:I10").Locked = True
s_worksheet.Protect "admin"
End If
Case Else: End
End Select
Many thanks for you help in advance