Lock Workbook upon opening based on current date

[andrewb90]

Hello All,

I have a Excel sheet that I need to lock from editing if the current date is past 3/31/16. however if there is a "password" typed into "Setup!A12" then this would be ignored, then the code would need to see if the date has passed 7/31/16 then check "Setup!A12" for a different password, then a third time for with a different password and 12/31/16.
Also I'd like to have a message box appear in each of the options. However, if there is no value in A12 then I would like "message A" and if there is an incorrect value in A12, then I would like to type a different custom message.

I hope this makes sense and this is doable!


Thanks

Andrew

 

[mole999]

maybe start with this

Sub test()
If Sheets("setup").Range(A12) = "password1" Then
ActiveSheet.Protect Password:="password"
Exit Sub
End If
If Sheets("setup").Range(A12) = "password2" Then
ActiveSheet.Protect Password:="password"
Exit Sub
End If
If Sheets("setup").Range(A12) = "password3" Then
ActiveSheet.Protect Password:="password"
Exit Sub
End If

End Sub

 

[andrewb90]

I don't think that's quite what I'm looking for. When the user opens the workbook this code should run a check and if the the date is passed my set date then it will check if the passcode is correct in A12, if it is, then nothing happens, and if not then the workbook is locked and unable to use (I figured I can hide all the sheets and protect the structure so that the sheets can't be accessed). This check repeats itself if the date is past the second date, and again the third date.

 

[mole999]

it was merely an outline, to do that you create it as a workbook open event, then set the parameters for one as between the first date and the second, then if it dosen't meet that criteria it would check your second set onwards, i wasn't pulling that fish in

 

[andrewb90]

That's good, I'm starting to figure it out. I have this so far, but it keeps giving me the wrong result:

Private Sub Workbook_Open()'MsgBox "test"


If CDate("03/31/2016") > Now() Then
MsgBox "april"


'If Date < 3 / 31 / 16 Then
'MsgBox "march"
End If
End Sub

Since it is november, the april message box shouldn't appear because the current date is earlier that the date listed. Am I missing something?

 

[mole999]

try 2015 rather than next year

 

[mole999]

you'll get to a version of
If Now() > CDate("03/31/2015") And Now() < CDate("12/31/2016") Then

 

[andrewb90]

Ok So I've updated my code, and from what I understand and searched the help functions on, my if statements seem to work (which clearly suggests that they don't), but I'm not sure what I'm doing wrong?

Private Sub Workbook_Open()'MsgBox "test"




'change 2nd date to 4/30/16 both times
If Now() > CDate("01/01/2000") And Now() < CDate("11/20/15") Then
    MsgBox "good" 'Delete this later


ElseIf Now() > CDate("11/20/15") Then
    MsgBox "Bad" 'remove later
    If Sheets("ttt").Range(A12) = "123" Then
    MsgBox "looks good for now."
    ElseIf Sheets("ttt").Range(A12) = "456" Then
    MsgBox "looks great!"
    End If






End If


End Sub

Thanks!

Andrew