See in-line responses..............
On Fri, 30 Nov 2007 13:35:38 +0000, Colin Hayes
wrote:
Hi
OK Thanks. I thought I'd try the one below.
I have a couple of questions though , if you could advise ;
The workbook it's protecting is called 'Receipts' - do I need to put
this anywhere in the code?
No. Thisworkbook is the workbook you are running the Sub on.
If I use this on more than one wb , do I need to change the name of the
file it stores , so as not to overwrite it.
No changes necessary as far as I can tell.....Thisworkbook refers to whatever
workbook the code is in.
I assume the file it is
storing is 'Expiration date'. It's not clear to me where it stores the
file....
Expiration Date is a named range which holds the date to expire. The code
creates this named range.
The line...........Private Const C_NUM_DAYS_UNTIL_EXPIRATION = 90 must be
placed above the line..........Sub TimeBombMakeReadOnly()
Also , I'm not sure where I should be placing the code in the workbook.
Under the tab at the bottom , or under the Excel symbol at top-left of
the sheet...?
Alt + F11 to open
VB Editor
CTRL + r to open Project Explorer if not visible.
Right-click on your workbook and InsertModule.
Paste code into that module with the edits above.
Save the workbook then run the macro which sets the time to expire as 90 days
from when you run the macro.
Have you downloaded the sample workbook from Chip's site to see how he has done
it?
Gord
Sub TimeBombMakeReadOnly()
''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''
' TimeBombMakeReadOnly
' This procedure uses a defined name to store the expiration
' date and if the workbook has expired, makes the workbook
' read-only.
''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''
Dim ExpirationDate As String
Dim NameExists As Boolean
On Error Resume Next
ExpirationDate = Mid(ThisWorkbook.Names("ExpirationDate").Value, 2)
If Err.Number < 0 Then
'''''''''''''''''''''''''''''''''''''''''''
' Name doesn't exist. Create it.
'''''''''''''''''''''''''''''''''''''''''''
ExpirationDate = CStr(DateSerial(Year(Now), _
Month(Now), Day(Now) + C_NUM_DAYS_UNTIL_EXPIRATION))
ThisWorkbook.Names.Add Name:="ExpirationDate", _
RefersTo:=Format(ExpirationDate, "short date"), _
Visible:=False
NameExists = False
Else
NameExists = True
End If
''''''''''''''''''''''''''''''''''''''''''''''''' '''''
' If the today is past the expiration date, make the
' workbook read only. We need to Save the workbook
' to keep the newly created name intact.
''''''''''''''''''''''''''''''''''''''''''''''''' '''''
If CDate(Now) = CDate(ExpirationDate) Then
If NameExists = False Then
ThisWorkbook.Save
End If
ThisWorkbook.ChangeFileAccess xlReadOnly
End If
End Sub
Thanks again.
In article , Gord Dibben
<gorddibbATshawDOTca@?.? writes
Colin
See Chip Pearson's site for creating a Time Bombed workbook.
http://www.cpearson.com/excel/workbooktimebomb.aspx
Gord Dibben MS Excel MVP
On Fri, 30 Nov 2007 01:52:58 +0000, Colin Hayes
wrote:
Hi
I need to have an excel file 'expire' 3 months after first installation.
Is it possible to build something into the program so that it will read
the date on first use and stop functioning after a set time , perhaps
with a popup too?
Any help appreciated.
Thanks