Solved: Pls explain time limit in code

[ndendrinos]

I need an explanation on the time limit in this code.
The way I read it (probably wrong) I interpret it to be like this.
If the date is 14 days past starting date (19-06-2006) then show the promt.
Question : Why then today when I run the code do I not get to see the prompt ?
Many thanks

[vba]Private Sub Workbook_Open()
Dim incRange As Range
Dim StartDate As Date

Set incRange = Range("Increment")
StartDate = "19/06/06" 'Date delivered to customer

Sheets("hidden").Visible = xlVeryHidden
incRange = incRange + 1
Me.Save
If incRange > 10 Or Date - StartDate > 14 Then
Application.DisplayAlerts = False
MsgBox "over the use limit, contact owner"
Me.Close
End If

End Sub[/vba]

[Bob Phillips]

Don't know, it works fine for me.

Do you have a sheet named 'hidden'?

[Norie]

StartDate is a string not a date as far as I can see.

Perhaps that's causing a problem.

[ndendrinos]

to xld: yes I have the sheet "hidden" it increments the times the files is opened but still works with the date (string) as shown in the code.
To Norie : how can I then replace it with a date to test it and see if it works ?

Also is my interpretation of the code correct ?

[mdmackillop]

I'm seeing the message box. Have you tried stepping throught code and using the Watch window to check the values?

[ndendrinos]

Better still here is the file

Sorry Malcom .. was typing while you where posting ... I will try

[ndendrinos]

Tried and all appears normal ... the prompt shows on the 11th time the user opens the file ... but with the start date in there and again the way I understand it I should see the promt from the very start no? 19/06/06 = June 19th 2006 + 14 days = July 03 2006 ... we are Nov 10th 2006 ... so I must be interpreting the meaning in the code wrongly.

[ndendrinos]

I think I've got it now ... must be that the original code was posted from Europe ... I've changed it to 06/16/06 and it works. If I'm wrong please bring it to my attention ... I will mark it solved but will check just in case

[Norie]

The code works for me too.

Where are you located?

Perhaps it's an issue with your date settings.

In Europe what might be happening is VBA is recognizing the string "19/06/06" as a valid date and therefore converting it to a 'true' date value.

Where the date format is different it might not be doing that.

I would suggest you take a look at the DateValue or DateSerial functions to set the date.

[ndendrinos]

Hello Norie and thank you for the followup.
I'm in Canada ... I just changed 19/06/06 to 10/20/2006 and it works.
If you have the time and would like to explain in details what you mean you will
do me a great favour as the file might be sent to Europeans as well.
Many thanks
Nick

[Norie]

Nick

Using DateSerial you could set the date like this.

StartDate = DateSerial(2006, 10, 20)

Which would be 20th November 2006.

[Charlize]

I need an explanation on the time limit in this code.
The way I read it (probably wrong) I interpret it to be like this.
If the date is 14 days past starting date (19-06-2006) then show the promt.
Question : Why then today when I run the code do I not get to see the prompt ?
Many thanks

[vba]Private Sub Workbook_Open()
Dim incRange As Range
Dim StartDate As Date

Set incRange = Range("Increment")
StartDate = "19/06/06" 'Date delivered to customer

Sheets("hidden").Visible = xlVeryHidden
incRange = incRange + 1
Me.Save
If incRange > 10 Or Date - StartDate > 14 Then
Application.DisplayAlerts = False
MsgBox "over the use limit, contact owner"
Me.Close
End If

End Sub[/vba]

when you want 19th july use #06/19/2006#. That will even work in Europe (even in Belgium).

Charlize

[mdmackillop]

Tricky things dates!

Code:
StartDate = DateSerial(2006, 10, 20)

Which would be 20th November 2006.

and

when you want 19th july use #06/19/2006#.

Charlize

[Charlize]

Indeed, maybe time to sleep ...

[ndendrinos]

Norie, Charlize is Malcom having fun at your expense?
Do I understand that both of you were distracted and that :
[VBA]

StartDate = DateSerial(2006, 10, 20)

Which would be 20th November 2006.[/VBA]
S/B 2006,11,20

and
[VBA]when you want 19th july use #06/19/2006#.[/VBA]
S/B #07/19/2006#

Or am I misssing something here ?

[Charlize]

You the nail.

Charlize

[mdmackillop]

Norie, Charlize is Malcom having fun at your expense?