VBA close workbook after a date

[CraigG]

Hi,

I have the following code:

Private Sub Workbook_Open()
Doit
End Sub


Sub Doit()


MyDate = Date - #12/31/2016#
If Date > MyDate Then
MsgBox "This workbook is closed"
ThisWorkbook.Close False
End If
End Sub


Can anyone help me where I'm going wrong? It gives the message box even if the date is in the future, eg, 31 December 2016 (which is after today 22 May 2015). I only want the message once the date has past.

Thanks.

 

[steve the fish]

Does this help?

 Sub Doit()

 myDate = CDate("21/05/2015")
 
 If Date > myDate Then
    MsgBox "This workbook is closed"
    ThisWorkbook.Close False
 End If
 
 End Sub

Change the date to whatever date you need

 

[mrmmickle1]

I've read several articles regarding dates and I can't remember the specifics but my general thinking is this:

Dates are read as Serial Numbers

5/22/2015 = 42146
12/31/2016 = 42735

42146-42735 = -589

Serial Dates Start at 1 So 1 = 1/1/1900

In your example

-589 = 5/20/1898

So....

5/22/2015 > 5/20/1898

When doing Date Intervals you can use the DateDiff() Function.. It may be beneficial to look into it depending on your goal.

This would work for your purposes:

[COLOR=#0000ff]Sub[/COLOR] Tester()

  [COLOR=#0000ff]  Dim [/COLOR]MyDate [COLOR=#0000ff]As Date[/COLOR]
  [COLOR=#0000ff]  Dim[/COLOR] NewDate [COLOR=#0000ff]As Date[/COLOR]

    NewDate = -589: MsgBox NewDate [COLOR=#008000]'This is to illustrate the Serial Date Issue....[/COLOR]
    MyDate = #12/31/2016#
    
    [COLOR=#0000ff]If[/COLOR] MyDate > Date [COLOR=#0000ff]Then[/COLOR]
[COLOR=#0000ff]        Exit Sub[/COLOR]
    [COLOR=#0000ff]ElseIf[/COLOR] Date > Date - MyDate [COLOR=#0000ff]Then[/COLOR]
        MsgBox "This workbook is closed"
        ThisWorkbook.Close [COLOR=#0000ff]False[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]

[COLOR=#0000ff]End Sub[/COLOR]

I'm sure someone else on the Message Board can explain better. Maybe someone can assist my explanation.

 

[CraigG]

Perfect! Thanks

Does this help?

 Sub Doit()

 myDate = CDate("21/05/2015")
 
 If Date > myDate Then
    MsgBox "This workbook is closed"
    ThisWorkbook.Close False
 End If
 
 End Sub

Change the date to whatever date you need