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>

 

[mtsf26]

HI Dmt32,
thanks for replying mine and change that code,i just curious is this code have limited display?since just able to show 12 rows only or maybe something wrong with my end?

 

[arunrana]

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 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

Hi,
Above VBA is working fine with new sheet. However I face 2 problems, When VBA runs with my customised sheet/ workbook.

1. Customised Sheet1 (or any name) having follwong data-
Coloum D Coloum L Coloum M
Name of account Application Date Maturity Date
Date in Coloum M arrive through formulas =IF(L5="","",EDATE(L5,$I$2)-1)
When Macro run for Coloumn M, its shows error "Type mismatch".
　
2. When VBA run in new sheet or workbook, Popup Message window show result for limited Rows only. Can we increase the limit.

Can anyone help, Pls

 

[hasiniraj]

With this code the I'm getting an error as "settimer" sub or function not defined. Any help on this?

 

[hasiniraj]

With this code the I'm getting an error as "settimer" sub or function not defined. Any help on this?

I'm using office 2010

 

[My Aswer Is This]

I would suggest you make a new posting and tell us what your wanting. You can see this is a very old posting.

I'm using office 2010

 

[JackDanIce]

Since your question relates to a specific comment in dave3009's post #3 within this thread, he suggests some code and I don't think you've not copied all of it, specifically the part highlighted in blue that relates to settimer. All of the code below needs to be in the same module:

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