Dec 16 2020 05:35 PM
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)
Dec 16 2020 11:32 PM
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.
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)
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.
Dec 17 2020 01:26 PM
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
Dec 20 2020 01:43 AM
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!