Pop up based on criteria
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. |
Pop up based on criteria
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 |
Pop up based on criteria
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. |
Pop up based on criteria
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 |
Pop up based on criteria
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 |
Pop up based on criteria
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 |
Pop up based on criteria
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. |
Pop up based on criteria
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 |
Pop up based on criteria
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 |
Pop up based on criteria
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 |
All times are GMT +1. The time now is 02:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com