How to have a worksheet become locked after a certain date (and possibly time)

[kiddbran10]

Hey everyone,

I've been scrolling around throughout the forums trying to find a piece of code that will help me with my situation, but have been unable to get any suggestions to work with my workbook. That may be due to it not working with my workbook, or because I can't figure out how to properly code in excel.

To explain my situation: I have a daily log that needs to be filled out on a daily basis, the workbook has 31 sheets (one for each day). On each sheet, I put that sheets date (Example: On May 1, the cell will say 2018-05-01, on the sheet for May 2 it will say 2018-05-02) into cell T1.

The following code is what I've been trying to run

Private Sub Workbook_Open()    If Sheets("1st").Cells(20, 1).Value >= Date And Sheets("1st").Cells(20, 1).Value <= Date + 1 Then
        ActiveWorkbook.Protect Password:="password"
        MsgBox "This workbook is locked, please contact xxx@xxx.com"
    Else
        'MsgBox "unlocked"
    End If
End Sub

Where "1st" is equal to the first sheet in the workbook named "1st", and where cell "20, 1" is equal to cell T1, and where date +1 is supposed to make the sheet lock the following day.

Bonus question, can I have the sheet lock at a certain date and time, as our day's closing time is 7am rather than midnight.

Thanks in advance for all your help!

 

[kiddbran10]

Just as an update, with a lot of playing around, I got the below code to do pretty much what I wanted.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)'Excel 10 Tutorial
    If Range("T1").Value < Date - 1 Then
        ActiveSheet.Protect Password:="123456"
        MsgBox "Only today's log can be edited!", vbInformation, "Protected Document"
    Else
    End If
End Sub

 

[Yongle]

Bonus question, can I have the sheet lock at a certain date and time, as our day's closing time is 7am rather than midnight.

Try

If (Range("T1").Value + Time) < (Date - 1) + (7 / 24) Then

- the brackets are not necessary but make what is being tested more obvious
- above amendment to your code results in yesterday's worksheet being locked at 7am today
- amend to suit your specific needs

or you could use NOW instead of both DATE and TIME, like this

If Range("T1").Value + (7 / 24) < (Now - 1) Then

Alternatively amend the value in T1 to hold both date and time
- instead of a date in T1
- place date & time value in T1
- ie 7am on each date (by adding 7/24 to the cell)
- the test could be simplified to

If Range("T1").Value  < (Now - 1) Then