Hi There,

If anyone could helip me then it would be a great help.

I have 2 columns

Order No. Start Date & Time End Date & Time
125463 4/10/2007 14:44 4/12/2007 14:44
451263 4/11/2007 14:44 4/13/2007 14:44


Is there any VBA code which can show a pop up if the differend between the
end date and now is less than 3 hours.
the pop up should contain the order nos mentioned.

thanks a lot in advance.

You didn't say anything about the user having to do anything to get
the pop-up or how often it could occur. The following requires the
user to start the timer, which will fire every 15 minutes. You could
start the process from Workbook_Open().

Sub StartTimer()
Application.OnTime Now() + TimeValue("00:15:00"), "CheckEndTime"
End Sub

Sub CheckEndTime()
Dim iEnd As Integer
Dim c As Range
Dim rng As Range
Dim ws As Worksheet

Set ws = Sheets("Sheet1")
iEnd = ws.Range("C1").End(xlDown).Row
Set rng = ws.Range("C2:C" & iEnd)
For Each c In rng
If c - Now() < 0.125 And c - Now() 0 Then _
MsgBox "Order " & c.Offset(0, -2) & " has end time " & c
Next c
Application.OnTime Now() + TimeValue("00:15:00"), "CheckEndTime"
End Sub

Hth,
Merjet

You could highlight them with conditional formatting, using a formula of

=($C2-$B2)*24<3

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jitendra Kumar" wrote in message
...
Hi There,

If anyone could helip me then it would be a great help.

I have 2 columns

Order No. Start Date & Time End Date &
Time
125463 4/10/2007 14:44 4/12/2007
14:44
451263 4/11/2007 14:44 4/13/2007
14:44


Is there any VBA code which can show a pop up if the differend between the
end date and now is less than 3 hours.
the pop up should contain the order nos mentioned.

thanks a lot in advance.

Hi Merjet,

Many thanks for the help and sorry for providing incomplete information.
I pasted the below code in a module and in workbook_open event I pasted the
sub name "StartTimer" but unfortunately it is not working.

I don't know why it is giving an error.
please help.

Regards,

"merjet" wrote:

You didn't say anything about the user having to do anything to get
the pop-up or how often it could occur. The following requires the
user to start the timer, which will fire every 15 minutes. You could
start the process from Workbook_Open().

Sub StartTimer()
Application.OnTime Now() + TimeValue("00:15:00"), "CheckEndTime"
End Sub

Sub CheckEndTime()
Dim iEnd As Integer
Dim c As Range
Dim rng As Range
Dim ws As Worksheet

Set ws = Sheets("Sheet1")
iEnd = ws.Range("C1").End(xlDown).Row
Set rng = ws.Range("C2:C" & iEnd)
For Each c In rng
If c - Now() < 0.125 And c - Now() 0 Then _
MsgBox "Order " & c.Offset(0, -2) & " has end time " & c
Next c
Application.OnTime Now() + TimeValue("00:15:00"), "CheckEndTime"
End Sub

Hth,
Merjet

What do you mean by "not working" and "giving an error"? If Excel is
giving an error message, what is it and on what line of code? If
nothing is happening, does your data have an end time within 3 hours?
If it doesn't, nothing will happen. Are you waiting 15 minutes?

I tested the code (with "00:00:10" rather than "00:15:00") with the
following in the ThisWorkbook module and it worked fine for me.

Private Sub Workbook_Open()
StartTimer
End Sub


Hth,
Merjet

Hi Merjet,

Thanks, It's working now. but I want it to work in a different way.
Now, It is poping up when the end time has passed.
I want it to pop when there is 3 hours left to reach the end time.
I mean it should throw a reminder so that the order should not cross end time.

Please be so kind to explain the logic of 0.125 which you have used in the
code.

Many thanks in advance and kind regards,
Jitendra

"merjet" wrote:

What do you mean by "not working" and "giving an error"? If Excel is
giving an error message, what is it and on what line of code? If
nothing is happening, does your data have an end time within 3 hours?
If it doesn't, nothing will happen. Are you waiting 15 minutes?

I tested the code (with "00:00:10" rather than "00:15:00") with the
following in the ThisWorkbook module and it worked fine for me.

Private Sub Workbook_Open()
StartTimer
End Sub


Hth,
Merjet

Hi Bob,

Thanks for the help but i need something in macro which could pop upp when
there is 3 hours left before the end time.

Kind Regards.
Jitendra

"Bob Phillips" wrote:

You could highlight them with conditional formatting, using a formula of

=($C2-$B2)*24<3

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jitendra Kumar" wrote in message
...
Hi There,

If anyone could helip me then it would be a great help.

I have 2 columns

Order No. Start Date & Time End Date &
Time
125463 4/10/2007 14:44 4/12/2007
14:44
451263 4/11/2007 14:44 4/13/2007
14:44


Is there any VBA code which can show a pop up if the differend between the
end date and now is less than 3 hours.
the pop up should contain the order nos mentioned.

thanks a lot in advance.

Now, It is poping up when the end time has passed.
I want it to pop when there is 3 hours left to reach the end time.
I mean it should throw a reminder so that the order should not cross end time.

Then my guess is that your code differs (logically) from mine. My
variable 'c' cycles through the end times. If c is past, then c -
Now() < 0 and the first if-condition fails.

Please be so kind to explain the logic of 0.125 which you have used in the
code.

0.125 = 1/8 3 hrs = 1/8 * 24 hrs

Hth,
Merjet

Hi Merjet,

I have used your code in some other similar sheet. The code used is given
below.

Sub StartTimer()
Application.OnTime Now() + TimeValue("00:00:10"), "CheckEndTime"
End Sub

Sub CheckEndTime()
Dim iEnd As Integer
Dim c As Range
Dim rng As Range

iEnd = Sheet1.Range("H5").End(xlDown).Row
Set rng = Sheet1.Range("H5:H" & iEnd)
For Each c In rng
If c - Now() 0 And c - Now() < 0.125 Then _
MsgBox c.Offset(0, -5) & " is about to cross SLA on " & Format(c,
"dd.mm.yy hh:mm:ss AM/PM"), vbInformation, "Please Check SLA"
Next c
Application.OnTime Now() + TimeValue("00:00:10"), "CheckEndTime"
End Sub

The code is showing a pop up but after showing the pop up it also shows an
error i.e.

Run-time error '13':
Type mismatch

After clicking on Debug, Following line of code gets selected.
If c - Now() 0 And c - Now() < 0.125 Then _

If you could help me out again this time, It would be a great act of
kindness to me.

Thanks a lot in advance,
Jitendra




"merjet" wrote:

Now, It is poping up when the end time has passed.
I want it to pop when there is 3 hours left to reach the end time.
I mean it should throw a reminder so that the order should not cross end time.

Then my guess is that your code differs (logically) from mine. My
variable 'c' cycles through the end times. If c is past, then c -
Now() < 0 and the first if-condition fails.

Please be so kind to explain the logic of 0.125 which you have used in the
code.

0.125 = 1/8 3 hrs = 1/8 * 24 hrs

Hth,
Merjet

Your code works fine for me when 'c' is a time, but I get a type
mismatch error if 'c' is not a time. So check the value of 'c' when
the error occurs.

Hth,
Merjet