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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
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,010
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,136,418
Messages
5,675,718
Members
419,584
Latest member
mrh8rr

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