Spreadsheet Expiration Date
Spreadsheet Expiration Date
(OP)
All:
I previously posted a request for some input on putting expiration dates on spreadsheets and got some great input that allowed me to get that set up.
A couple of follow up questions:
1) I have the code password protected in VBA, but is there a way to prevent users from simply clicking the disable macros tab on start up?
2) As a follow up to 1) above, a former poster had made the following recomendation - "I would put conditional formating that will make the cells all black if the NOW() function is pass certain date. And maybe put a red statement saying it expires. Make sure you put a password so people cant edit it". The question is how would I add conditional formatting? (at the risk of stating the obvious - I'm not an Excel or VBA expert)
The code I am using is (also from a helpful previous poster):
Auto_Open()
ActiveWorkbook.PrecisionAsDisplayed = False
If Date < #3/26/2009# Then Exit Sub
Bye_Message
With ThisWorkbook
.Saved = True
.ChangeFileAccess xlReadOnly
' Kill .FullName
.Close False
End With
End Sub
Bye_Message()
'Self-Destruct Final Message no response needed, just OK
ln1 = "This spreadsheet has a defined expiration date which has expired. After the expiration"
ln2 = "date the spreadsheet will not function. Your data will not be lost. Send the file to the"
ln3 = "author to request an updated copy."
In4= " "
ln5 = "Author is Greg Test (test@yahoo.com / www.test-consulting.com)
msg = ln1 & vbCr & ln2 & vbCr & ln3 & vbCr & ln4 & vbCr & ln5
style = vbOKOnly + vbInformation
Title = "International Test Consulting, LLC© December 2008, by Greg"
MsgBox msg, style, Title
End Sub
Any help would be appreciated.
Greg Lamberson, BS, MBA
Consultant - Upstream Energy
Website: www.oil-gas-consulting.com
I previously posted a request for some input on putting expiration dates on spreadsheets and got some great input that allowed me to get that set up.
A couple of follow up questions:
1) I have the code password protected in VBA, but is there a way to prevent users from simply clicking the disable macros tab on start up?
2) As a follow up to 1) above, a former poster had made the following recomendation - "I would put conditional formating that will make the cells all black if the NOW() function is pass certain date. And maybe put a red statement saying it expires. Make sure you put a password so people cant edit it". The question is how would I add conditional formatting? (at the risk of stating the obvious - I'm not an Excel or VBA expert)
The code I am using is (also from a helpful previous poster):
Auto_Open()
ActiveWorkbook.PrecisionAsDisplayed = False
If Date < #3/26/2009# Then Exit Sub
Bye_Message
With ThisWorkbook
.Saved = True
.ChangeFileAccess xlReadOnly
' Kill .FullName
.Close False
End With
End Sub
Bye_Message()
'Self-Destruct Final Message no response needed, just OK
ln1 = "This spreadsheet has a defined expiration date which has expired. After the expiration"
ln2 = "date the spreadsheet will not function. Your data will not be lost. Send the file to the"
ln3 = "author to request an updated copy."
In4= " "
ln5 = "Author is Greg Test (test@yahoo.com / www.test-consulting.com)
msg = ln1 & vbCr & ln2 & vbCr & ln3 & vbCr & ln4 & vbCr & ln5
style = vbOKOnly + vbInformation
Title = "International Test Consulting, LLC© December 2008, by Greg"
MsgBox msg, style, Title
End Sub
Any help would be appreciated.
Greg Lamberson, BS, MBA
Consultant - Upstream Energy
Website: www.oil-gas-consulting.com





RE: Spreadsheet Expiration Date
Leave one sheet visible, and on it put "Sorry, Expired" or whatever message you care.
Your AutoOpen code - upon confirming that things have not expired can hide the "expired" sheet and make the very hidden sheets visible.
If someone turns macros off all they see is the "expired" sheet.
It's not bullet proof, but it will discourage most folks.
RE: Spreadsheet Expiration Date
RE: Spreadsheet Expiration Date
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Spreadsheet Expiration Date
thread776-144608: Choose in wich computers a macro should run.
thread770-171136: keeping Spreadsheets within the office
thread770-217385: Expiration Date for Spreadsheet
This subject keeps coming up, partly because there is no perfect solution.