Date set to kill a ws

[joelle]

Hello Experts

I tried to set a time bomb to kill only one particular worksheet (named "gone") in my WB and I dont know what's wrong with the code below. The code does not execute even tho the date I put in is yesterday just to test it.
Is the date format incorrect?

Private Sub Worksheet_Deletion()
If Date < #7/12/2005# Then Exit Sub
Application.DisplayAlerts=False
Sheets("gone").Delete
Application.DisplayAlerts=True
End Sub

Or, 2nd option: How do I set an expiration date for the whole workbook so that when that date comes, the wb cant be open ???
---------------------------------------

Many thanks in advance,
Nee

[Scottie P]

Good day Nee!

Check out this Kb entry by Brettdj: Expiring Workbook

Scott

[joelle]

Hello Scott,

First, thanks a lot for the quick help!
I'll try it shortly and let you how it goes. However, with the
date format in your code (36525) how can I tell which date
it corresponds to, i.e. 7/12/2005 or 7/14/2005 (for tracking
purpose in the future).

But now I'm curious to know if Excel 2000 has its own feature to
allow users to set experiration to a wb by using the [Rights and Management] feature under "Permission" option (on the standard toolbar)
that is available in Excel 2003?? With this feature, then only you can open the wb when it has expired.

Very interesting if we can do this without having to use vba code.
What you think?

Thanks,
Nee

[Zack Barresse]

If you don't want to use serial numbers, you can just type in the date surrounded by the # sign ...

Option Explicit

Sub FooFooTest()
    Call MsgBox("Today's Date is: " & Format(#4/18/2005#, "mm/dd/yy"))
End Sub

Sub FooDooTest()
    Dim Expiration As Date, Msg As String
    Expiration = #1/1/2005#
    Msg = "YEAR: " & vbTab & Format(Expiration, "yyyy") & vbNewLine & _
        "MONTH: " & vbTab & Format(Expiration, "mmmm") & vbNewLine & _
        "DAY: " & vbTab & Format(Expiration, "dd") & vbNewLine & _
        vbTab & Format(Expiration, "dddd")
    MsgBox Msg
End Sub

This does depend on your system date/time (regional) settings.

[joelle]

Great! Thanks a lot Zack.
I'll plug your codes in now ... pls wish me luck.

Nee

[Zack Barresse]

Another expample, which some people like this more, would look like ...

Sub FooZooTest()
    Dim Expiration As Date
    Expiration = DateSerial(2005, 1, 1)
    MsgBox Format(Expiration, "mm/dd/yy")
End Sub

You just need to remember that Dates in Excel (and Excel VBA) are still Serial Numbers.

[joelle]

Hello Zack,

I have not tried the 1st set of code you posted, but you are right,
I like your 2nd set of codes (more concise) and they work beautifully.
Thanks again.

Now, I'm getting greedy -- what you think or do you have any hint about
Excel feature of setting an expiration date for the whole workbook without
having to use vba. I heard it is only available from excel 2003 and newer.
I'm just curious, please ignore me if it requires time to research.

My regards,
Nee

[Ken Puls]

Good day Nee!

Check out this Kb entry by Brettdj: Expiring Workbook

Scott

Cough, cough! Is that the one that emails it to Scott for "troubleshooting"?

[joelle]

Hello Ken,

You lost me with your msg or it wasn't for me?

Nee

[Ken Puls]

Sorry, Nee. I'm just poking a little fun at my friend Scott. He's got an itchy Delete finger sometimes.... If this reply goes missing, you'll know what happened.