Auto-lock the sheet after past a certain date

[norisk]

Hello sir

I badly need help that automatically locks certain sheets after a specific date. For instance, under January, there were cells that I protected (pw 1234), so no one can change the formula. By February 7, the sheet for Jan will automatically lock, and no one can modify it. The same with the other months, it will be closed every 7th day of the following month.

Hopefully, you can help me. please see the attached file.

Thank you

 

[vletm]

norisk
You could test something like this ...
Note: This uses Your protection settings.
About Your: no one can modify it ... it'll be possible to modify those.

 

[norisk]

Thank you sir.. I tried but I can still modify January and Feb.

 

[Marc L]

Hello, a weird conception has there is no year reference so hoping in december you never have to prepare the next year workbook ! …​

​

 

[norisk]

Hello sir I have attached another file. Would you help me with the VBA? I have added the year on the upper portion

 

[Marc L]

As the actual worksheets password is very not 1234 so just wait until I guess ‼ … ​

I can still modify January and Feb.

So which cells you need to lock ?​

​

 

[norisk]

hi sir Marc

Sorry.. Kindly check again the file. Please try 070715 or 1234.. I wanted to lock the entire sheet every 7th day of the following month. For instance January should be locked by feb 7

 

[Marc L]

For instance Feb month must be lock as well or the dates logic is not the same between Paris and Riyadh ?‼​

​

 

[norisk]

yes sir it should be locked as well. The Feb, and so forth should be locked every 7th day of the following month.

 

[Marc L]

According to your post #5 attachment for starters paste this workbook event VBA procedure only to ThisWorkbook module​

then save your workbook as binary format .xlsb and close it :​

Private Sub Workbook_Open()
        On Error Resume Next
    For S% = 3 To Sheets.Count
        With Sheets(S)
                If .[AH7] + 7 > Date Then Exit Sub
            If .[B13].Locked * .ProtectContents = 0 Then
               .Protect "070715", UserInterfaceOnly:=1
                If Err.Number Then Beep: Exit Sub
               .UsedRange.Locked = True
            End If
        End With
    Next
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[vletm]

norisk
As I wrote: Note: This uses Your protection settings.
This locks 'Month'-sheets too.

 

[norisk]

According to your post #5 attachment for starters paste this workbook event VBA procedure only to ThisWorkbook module​

then save your workbook as binary format .xlsb and close it :​

Private Sub Workbook_Open()
        On Error Resume Next
    For S% = 3 To Sheets.Count
        With Sheets(S)
                If .[AH7] + 7 > Date Then Exit Sub
            If .[B13].Locked * .ProtectContents = 0 Then
               .Protect "070715", UserInterfaceOnly:=1
                If Err.Number Then Beep: Exit Sub
               .UsedRange.Locked = True
            End If
        End With
    Next
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

hello Sir.. thank you.. I tried to use it but I can still modify for January and Feb

 

[vletm]

norisk
Did You test my sample (#11 reply)?
HInts:
It works only
... if that code has saved in correct place
... while open that file
... with unprotected sheet
... Your no one can modify it ... means that the password could be random/unknown.

 

[Marc L]

I tried to use it but I can still modify for January and Feb

Not on my side with your attachment so what is your bad ?!​

Did you try at least with exactly the same post #5 attachment ?​

For good enough readers only so did you follow at least the post #10 directions (in particular the dark red) ?​

​

 

[norisk]

Not on my side with your attachment so what is your bad ?!​

Did you try at least with exactly the same post #5 attachment ?​

For good enough readers only so did you follow at least the post #10 directions (in particular the dark red) ?​

​

Yes sir..Kindly see the attached file if what I did is correct. the month of feb can still be modified

 

[norisk]

norisk
Did You test my sample (#11 reply)?
HInts:
It works only
... if that code has saved in correct place
... while open that file
... with unprotected sheet
... Your no one can modify it ... means that the password could be random/unknown.

Yes sir I have tried to open and check it. this will work only based on my protected setting. The cells and areas which I have locked cannot be modified if past date. However, if you will check for the month of March it was locked as well which supposedly will only be locked by April 7.

 

[Marc L]

Yes sir..Kindly see the attached file if what I did is correct. the month of feb can still be modified

Your bad ‼ As you felt in the bad reader trap !​

So just delete Module1 and as yet written in post #10 paste the code where it must me aka in ThisWorkbook module …​

​

 

[vletm]

norisk
I would modify only details, which You would let me know.
It's You - who'll do the most of testing & verifying.
Q: Why do would like to have monthly sheets instead all data in one sheet and 'summary' could make eg with Pivot-table?

 

[norisk]

Thank you.. Both your suggestions were working.