delete workbook after a certin time period

[austenr]

can this be done in code?

[Simon Lloyd]

This should do it![VBA]
Private Sub Workbook_Open()
If Date >= "26/09/08" Then
MsgBox "Trial period has expired please call John Doe", vbOKOnly, "Trial Period Expired"
Call KillActive
End If
End Sub

Sub KillActive()
Dim sName As String
On Error Resume Next
sName = ThisWorkbook.FullName
Application.DisplayAlerts = False
ThisWorkbook.ChangeFileAccess xlReadOnly
Kill sName
Application.DislayAlerts = True
ThisWorkbook.Close SaveChanges:=False
Application.Quit
End Sub[/VBA]

[austenr]

thanks. that should do it

[grichey]

this would only work if they enabled vba when they opened the workbook though, correct?

[Simon Lloyd]

Yes correct you would have to forc macro use too by hiding all sheets (xlveryhidden) except one and unhiding during the workbook open event.

Add o worksheet call it Welcome and use this code (adapt to use the trial period code within it!)
[VBA]Private Sub Workbook_Open()
Dim Sht As Worksheet
Application.ScreenUpdating = False
For Each Sht In Sheets
If Not Sht.Name = "Welcome" Then
Sht.Visible = xlSheetVisible
End If
Next
Sheets("Welcome").Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Sht As Worksheet
Application.ScreenUpdating = False
Sheets("Welcome").Visible = xlVisible
For Each Sht In Sheets
If Not Sht.Name = "Welcome" Then
Sht.Visible = xlSheetVeryHidden
End If
Next
Application.ScreenUpdating=True
End Sub [/VBA]

[grichey]

ha. I've never done that before using very hidden. slick.

[grichey]

Where does that code go? I put it in a module and it doesn't appear to do anything when i close a workbook or open it.

[grichey]

I put it in the workbook and it almost works.

When I close, I get Run-time error '1004': Unable to set the Visible property of the Worksheet class.

Thoughts?

[grichey]

Was just missing the sheet from x1SheetVisible.

[VBA]Private Sub Workbook_Open()
Dim Sht As Worksheet
Application.ScreenUpdating = False
For Each Sht In Sheets
If Not Sht.Name = "Welcome" Then
Sht.Visible = xlSheetVisible
End If
Next
Sheets("Welcome").Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Sht As Worksheet
Application.ScreenUpdating = False
Sheets("Welcome").Visible = xlSheetVisible
For Each Sht In Sheets
If Not Sht.Name = "Welcome" Then
Sht.Visible = xlSheetVeryHidden
End If
Next
Application.ScreenUpdating=True
End Sub [/VBA]

[GTO]

@Simon:

Hi Simon,

Say, reference using the string...

This should do it![vba]
Private Sub Workbook_Open()
If Date >= "26/09/08" Then
MsgBox "Trial period has expired please call John Doe", vbOKOnly, "Trial Period Expired"
Call KillActive
End If
End Sub
[/vba]

...I tried a couple of tests. Here (U.S.), if I hover over Date while stepping thru, it reads "9/21/2010" due to m/d/yyyy settings. So I changed the string to ""09/26/08" and it passed.

The trouble is that so did "09/26/10". Only if I put the exact same formatting to the string, "9/26/2010" does it recognize that the date is farther out.

Anyways, my suggestion would be to use DateSerial, because AFAIK, this will bypass any formatting/local settings issues, as we are comparing a Date to a Date.

    If Date >= DateSerial(2010, 9, 26) Then

A great day to all,

Mark

[Simon Lloyd]

Mark, good catch, i didn't think of the code being used outside of the uk or uk date formatting, just no forward planning on my behalf