If Now > 12/ 31/2006 Then...

BoilerBob

Board Regular
Joined
Apr 10, 2006
Messages
98
This is probably simple, but I did a board search for Now and the search did not return any results.

I am creating a spreadsheet that I would like to 'retire' after a certain period of time via VBA. I put the following in ThisWorkbook

Private Sub Workbook_Open().....
If Now > 12 / 31 / 2006 Then
Worksheets("FileExpired").Visible = True
Worksheets("Information").Visible = xlSheetVeryHidden
Application.Wait Now + TimeValue("00:00:04")
Application.DisplayAlerts = False
'Is it possible to delete "Information" sheet?
Workbook.save
Application.Quit

Else
Worksheets("Information").Visible = True
Worksheets("FileExpired").Visible = xlSheetVeryHidden
End If


I think my logic is correct, but my coding for the now function is off.



Any help is greatly appreciated...
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,351
Office Version
  1. 365
Platform
  1. Windows
It's not the Now function that's the problem it's 12/31/2006.

VBA won't regard that as a data, it'll be regarded as a calculation.

ie 12 divided by 31 divided by 2006

Try this.
Code:
If Now > #12/31/2006# Then
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,053
Office Version
  1. 365
Try:

If Date > DATEVALUE("12/31/2006") Then
 

BJungheim

Well-known Member
Joined
Jul 24, 2004
Messages
914
Try:
Code:
IF Date > DateSerial(2006,12,31) Then

As far as your question:
'Is it possible to delete "Information" sheet?
Just change:
Code:
Worksheets("Information").Visible = xlSheetVeryHidden
to
Code:
Worksheets("Information").Delete
 

Forum statistics

Threads
1,171,161
Messages
5,874,113
Members
433,026
Latest member
LawnmowerITGuy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top