PDA

View Full Version : [SOLVED] Date set to kill a ws



joelle
07-13-2005, 09:02 AM
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
07-13-2005, 09:30 AM
Good day Nee!

Check out this Kb entry by Brettdj: Expiring Workbook (http://www.vbaexpress.com/kb/getarticle.php?kb_id=540)

Scott

joelle
07-13-2005, 10:14 AM
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
07-13-2005, 10:20 AM
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
07-13-2005, 10:30 AM
Great! Thanks a lot Zack.
I'll plug your codes in now ... pls wish me luck.

Nee

Zack Barresse
07-13-2005, 10:32 AM
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
07-13-2005, 02:09 PM
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
07-13-2005, 02:45 PM
Good day Nee!

Check out this Kb entry by Brettdj: Expiring Workbook (http://www.vbaexpress.com/kb/getarticle.php?kb_id=540)

Scott

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

joelle
07-13-2005, 03:12 PM
Hello Ken,

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

Nee

Ken Puls
07-13-2005, 04:37 PM
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. :rotlaugh: