PDA

View Full Version : Solved: Pls explain time limit in code



ndendrinos
11-12-2006, 08:07 AM
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

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

Bob Phillips
11-12-2006, 09:38 AM
Don't know, it works fine for me.

Do you have a sheet named 'hidden'?

Norie
11-12-2006, 10:19 AM
StartDate is a string not a date as far as I can see.

Perhaps that's causing a problem.

ndendrinos
11-12-2006, 10:44 AM
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
11-12-2006, 10:50 AM
I'm seeing the message box. Have you tried stepping throught code and using the Watch window to check the values?

ndendrinos
11-12-2006, 10:56 AM
Better still here is the file

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

ndendrinos
11-12-2006, 11:13 AM
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
11-12-2006, 11:23 AM
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
11-12-2006, 12:17 PM
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
11-12-2006, 12:26 PM
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
11-12-2006, 12:47 PM
Nick

Using DateSerial you could set the date like this.


StartDate = DateSerial(2006, 10, 20)
Which would be 20th November 2006.

Charlize
11-12-2006, 04:39 PM
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

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
when you want 19th july use #06/19/2006#. That will even work in Europe (even in Belgium).

Charlize

mdmackillop
11-12-2006, 04:41 PM
Tricky things dates! :rotlaugh:




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

Which would be 20th November 2006.

and

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

Charlize

Charlize
11-12-2006, 04:43 PM
Indeed, maybe time to sleep ...

ndendrinos
11-12-2006, 05:28 PM
Norie, Charlize is Malcom having fun at your expense?
Do I understand that both of you were distracted and that :


StartDate = DateSerial(2006, 10, 20)

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

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

Or am I misssing something here ?

Charlize
11-13-2006, 01:43 AM
You :hammer: the nail.

Charlize

mdmackillop
11-13-2006, 01:43 AM
Norie, Charlize is Malcom having fun at your expense?

:yes