Forum Discussion
Expiry date (12/31/202X) triggered by unknown start date (XX/XX/202X) of that year
Dear Community,
Retired Civil Engineer, working on a spreadsheet whose license, when activated (i.e. 04/15/202X) will expire at calendar's year-end, 12/31/2X.
I have successfully tested and placed individual cell Expiry dates within my five (5) spreadsheet(s), both formula and reference driven. It may be "over-kill", but I also wish to create a VBA code that will "KILL" the spreadsheets on 01/01/202X as a backup to my cell references.
My perfect world would state in MsgBox(s):
1) "This License is Valid till 12/31/202X" upon first date of activation and then
2) Remind them each day starting on 12/01/202X with a decreasing countdown of days that "This License Expires in XX days" (It's annoying if, when they activated the License on 04/15/202X, they receive a reminder every day thereafter.)
3) On 01/01/202X, they would be "Locked" out of the spreadsheet until they renew their License agreement
4) And a MsgBox would appear "You Must Contact ProTee to Renew Your License." anytime after the 01/01/202X lock-out
Thanks for your input and suggestions!
(.pdf's attached for additional clarity)
3 Replies
After I've examined both PDFs and VBA code, I would like to suggest few correction are as follows.
Replace
Range("D1").value =Date
with
Range("D1").value =Format(Date, "mm/dd/yyyy")
N.B.
- You may pick any Date format which ever is suitable.
- Using Format with Date is a wise move otherwise it's an optional.
Next is assign formula to the variable,, current code has wrong syntax, better use this.
Expiry =Application.DATE(Year(D1),Month(D1),Day(D1)+15)
- This will add 15 days to the Date value the code is accessing for cell D1.
Please remember that the DATEVALUE is not an appropriate to use, since it convert the DATE if as TEXT into NUMBER.
**Please confirm through REPLY whether this works for not, if works then you may accept my post as Best Solution as well Like.
First reply didn't seem to post @ 0830 my time.
Received the following Run-time error '438' (see attachment "Error Msg.")
Also attached a worksheet Code 2 copy of the revised syntax/code as instructed.
If it matters, I'm using Windows 10, MS Office v.18.2008.12711.0, MS Excel Office Home and Student 2019.
Does it matter what date format I use? I personally use 17 Dec 2020, but for the spreadsheet, cell D1, I used 12/17/2020.
Thanks, ProTee
Using FORMAT is an optional but is a good practice,, please share the WB with us and let me check it, to fix the error!
