Lock sheets automatically

[Thu Ng]

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

 

[Dave Peterson]

Maybe...

Dim s_worksheet As Worksheet

For Each s_worksheet In ActiveWorkbook.Worksheets
Select Case UCase(s_worksheet.Name)
Case "TS1", "TS2", "TS3"
If DateDiff("d", s_worksheet.Range("B12").Value, 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
End Select
Next s_worksheet

Using End as in:
Case Else: End

Is usually a very bad idea. It resets public/static variables. There are nicer
ways to exit a routine.

 

[JLatham]

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,D612,F6:F12,G6:G10,H6:H10,I6:I10").Locked = True
s_worksheet.Protect "admin"
End If
End Select
Next
End Sub