Forum Discussion
VBA Timebomb in Excel Spreadsheet
Thanks, I think the problem was that the ExpiredDate name refers to the date as a string.
The attached version refers to the date as a date. See if it works for you.
Both versions of the code that I posted are independent of the system date format of the user.
Your original version used the user's short date format, which might be different from that of another user - I think that caused the code to fail to work correctly.
I really Appreciate All the Help!
Just a Question, If You Had to Do it, How Would You Doing and Code IT?
- Thank You!
Yes I know this is really not what Excel is made for but it just a Stopgap till we can get a COM or DLL Add in to do it proper or a different format to get the Sheets into. 1. Try adding
Application.DisplayAlerts = False
at the beginning of the macro, and
Application.DisplayAlerts = True
at the end.
2. and 3. More reasons why you shouldn't do this at all.
Hi Hans,
Hope you doing well.
I played around with the Code and ways of By Passing it and ways to Prevent that from Happening and I just have a few more Questions:
Based on this code you gave:
Sub TimeBombMakeReadOnly()
''''''''''''''''''''''''''''''''''''''''''''''''''''''
' If today is past the expiration date, make the
' workbook read only.
''''''''''''''''''''''''''''''''''''''''''''''''''''''
If Date >= DateSerial(2021, 12, 31) Then
ThisWorkbook.ChangeFileAccess xlReadOnly
ThisWorkbook.Save
End If
End Sub
- Is there a Way to Suppress all Messages asking the User if they want to save or Warn them that the Status is being updated and just save the Workbook?
- When you do a Save As and give it a New File Name, this New Workbook can then be changed and saved, I think it is because the Code is only Called when the Workbook is opened, is there a way to call the code each time the user does something?
- Is there a Way to Disable all the Menus when the User Opens the Workbook and it is the Expiry Date, thus the User can't do anything, just view the Content and Exit or maybe allow them to view the content and print it?
Again Thank you for all the Help, it is Greatly Appreciated!!
I probably wouldn't do it at all - it's too easy to bypass the code (even if you protect the code with a password).