Popup reminder message three days before due date

[livanga]

hello Guru
my boss wants to have a reminder tool for air ticket and accomodation that can popup the reminder message 3 days before the due date and atlest two times a day.

the excel workbook has two sheets the reminder has to be set in the second sheet which has ten columns as showns below. i know the forums is full of guru so please help<TABLE style="WIDTH: 989pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1317><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4717" span=2 width=129><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><COL style="WIDTH: 124pt; mso-width-source: userset; mso-width-alt: 6034" width=165><COL style="WIDTH: 112pt; mso-width-source: userset; mso-width-alt: 5449" width=149><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5376" width=147><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 150pt; mso-width-source: userset; mso-width-alt: 7314" width=200><COL style="WIDTH: 115pt; mso-width-source: userset; mso-width-alt: 5595" width=153><TBODY><TR style="HEIGHT: 42pt" height=56><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #538ed5; WIDTH: 48pt; HEIGHT: 42pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=56 width=64>Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #538ed5; WIDTH: 97pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=129>Requestor</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #538ed5; WIDTH: 97pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=129>Expire Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #538ed5; WIDTH: 80pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=107>DSPN</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #538ed5; WIDTH: 124pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=165>Traveller's name </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #538ed5; WIDTH: 112pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=149>Hotel booked </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #538ed5; WIDTH: 110pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=147>Confirmation #</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #538ed5; WIDTH: 56pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=74>LPO #</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #538ed5; WIDTH: 150pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=200>confirmed used/unused </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #538ed5; WIDTH: 115pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=153>recommendation </TD></TR></TBODY></TABLE>

 

[UglyBunny]

Hello Livanga,

I'm only learning excel, but hopefully a guru will pop in later!

I don't know yet how to make reminder pop-up messages, but I can make this nifty if statement that will show a message in a cell when the ticket is about to expire. You could maybe change the font to bold and red if you want to make it stand out more.

=IF(A1<=TODAY()+3,A1-TODAY()&" days left!","")

Just change A1 to wherever the expiry date is =)

 

[dave3009]

With some VBA it could look like this, in a regular module put this.

Sub popup()
Dim lstRow As Long
Dim i As Long
Dim msg As String
msg = "The following items are almost due" & vbCrLf & vbCrLf
lstRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lstRow
    If Range("A" & i) - Date <= 3 Or Range("A" & i) - Date < 0 Then
        msg = msg & Range("B" & i).Value & " due in " & Range("A" & i) - Date & " Days" & vbCrLf
    End If
Next i
MsgBox msg
Call settimer
End Sub

Sub settimer()
Application.OnTime Now + TimeValue("02:00:00"), "popup"
End Sub

then in the ThisWorkbook module put this

Private Sub Workbook_Open()
Call popup
End Sub

 

[livanga]

Thanx dave3009. i copied all the codes to vb and workbook as you instructed that was fine. if i click on run macro in excel i get the error message. CANT EXECUTE CODE IN BREAK MODE

Please help again am more into IT issue and non in Programming

and am using office 2007 in win 7 Pro as well

 

[dave3009]

That error means you are in break mode in the VBE, you need to press the reset button.

 

[mtsf26]

[LEFT][COLOR=#333333]lstRow = Range("A" & Rows.Count).End(xlUp).Row
[/COLOR][COLOR=#333333]For i = 2 To lstRow[/COLOR][/LEFT]

Hi dave can you explain the function of this code? since i try to modify your code then implement to my worksheet but seems that i have problem regarding the row. please kindly help me out.Thanks

 

[mtsf26]

anyone can help me to explain this?

 

[mtsf26]

i keep getting runtime error 13 message. i think because of this code,anyone can help?

 

[dave3009]

That code finds the last row then starts a loop running from row 2 to the last row. I cannot see how a runtime 13 error could be caused by that, unless there is no data whatsoever in column A

 

[dmt32]

anyone can help me to explain this?

Perhaps as you are using 2007 you need t qualify the ranges to the worksheet. Hope Dave3009 does not mind but I have changed his code a little for you to try & see if helps. You will need to change worksheet name to sheet name you are reffering to.

Dave

Sub popup()
Dim lstRow As Long
Dim i As Long
Dim msg As String
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1") '<CHANGE as name required
rename as required

With ws
msg = "The following items are almost due" & vbCrLf & vbCrLf
lstRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To lstRow
If .Range("A" & i) - Date <= 3 Or .Range("A" & i) - Date < 0 Then
msg = msg & .Range("B" & i).Value & " due in " & .Range("A" & i) - Date & " Days" & vbCrLf
End If
Next i
End With
MsgBox msg
Call settimer
End Sub