VBA to manage workbook expiry date allowing for extensions

[Woodsie]

Hi all,
I hope you are well and staying safe.

I have been working on what should be a relatively straightforward piece of code but I can't get it right and I don't have much hair left to pull out!
Can someone take a look at the below please?

What I am trying to do is load a workbook, check today's date versus a defined expiry date (exdate), if the expiry date is in the future then continue to open the workbook. If it is in the past (i.e. workbook expired) then I would like two options, one is an admin password ("test" in the below text) to let me in, the other would be a password to extend the life ("extend" in the below text). My intention would be to permit three extensions (stored in a named range/cell called ExtensionCount) and each time the "extend" password is used the macro will +1 onto the cell value. If you go over 3 extensions then the macro would inform you via a msgbox and exit, if you are 3 extensions or below it lets you in.
I apologise in advance for my bad code, I'm chopping up someone else's solution to a simpler problem. I feel like it's pretty close but when I enter the extend password it is closing the workbook, I think I'm getting confused with the IFs and when to End If.

Thanks for any advice you could offer.

Sub Workbook_Open()
Dim exdate As Date, ws As Worksheet, PW As String

exdate = "02/02/2022"

If Date < expdate Then

Exit Sub
End If

If Date > exdate Then
MsgBox ("Workbook expiry date has passed, please enter password to unlock.")
PW = InputBox("Enter password:")

If PW = "test" Then
Exit Sub

Else

If PW = "extend" Then
With Sheets("Sheet1").Range("ExtensionCount")
    .Value = .Value + 1
End With
    ActiveWorkbook.Save
    
End If

If Sheets("Sheet1").Range("ExtensionCount") > 3 Then MsgBox ("Maximum Extensions Reached")
ActiveWorkbook.Close
End If

Else

Exit Sub

MsgBox ("The Password is incorrect, This file will now be closed.")
ActiveWorkbook.Close
End If
End Sub

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA to manage workbook expiry date allowing for extensions [SOLVED]
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.