Only Due Date Reminder as POPUP as excel opens

[jon112211]

Hello Friends,iam having a excel file with "Party name, Insurance companies name, Amount insured, Due date"Now i want a popup message each time i open the excelpopup message should contain only the "name of insurance company" whose due date is 3 day left to 0 days leftplz tell me step by step process as iam not an expert with VBSiam using Excel 2007

 

[mumps]

Hi jon. Try this code. It assumes that the name of the company is in column A and the date is in column D.

Sub Test()
    Dim bottomD As Integer
    bottomD = Range("D" & Rows.Count).End(xlUp).Row
    Dim c As Range
    For Each c In Range("D2:D" & bottomD)
        If c >= Date And c <= Date + 3 Then
            MsgBox c.Offset(0, -3)
        End If
    Next c
End Sub

 

[jon112211]

Hi jon. Try this code. It assumes that the name of the company is in column A and the date is in column D.

Sub Test()    Dim bottomD As Integer    bottomD = Range("D" & Rows.Count).End(xlUp).Row    Dim c As Range    For Each c In Range("D2:D" & bottomD)        If c >= Date And c <= Date + 3 Then            MsgBox c.Offset(0, -3)        End If    Next cEnd Sub

Thanks a lot BHAI.....it was of g8 help... but if possible plz,.. let me know if i can get an alert someting like dis "name of Company in colum A" is due by "No. of days".... example : if name of company is ABC and if its due by 2 days then alert should come like dis "ABC due in 2 days"..... and i want this POPUP to automatically come when i start this particular excel file....Thanks A Lot

 

[mumps]

Hi jon. Place the following code into the code module for 'ThisWorkbook' (not into a regular module). Then save the file, close it and re-open it. The alerts should come up automatically. By the way, who is BHAI?

Private Sub Workbook_Open()
    Dim bottomD As Integer
    bottomD = Range("D" & Rows.Count).End(xlUp).Row
    Dim c As Range
    For Each c In Range("D2:D" & bottomD)
        If c >= Date And c <= Date + 3 Then
            MsgBox c.Offset(0, -3) & " is due in " & c - Date & " days."
        End If
    Next c
End Sub

 

[jon112211]

mumps .....Thanks a lot..... u made it so simple and awesome.... great works... Thanks a lot........."Bhai" is actually an Hindi word which means "BROTHER"..........dont knw ur name so i jst......anywz...thanks a lot for that script......

 

[mumps]

It was my pleasure, BHAI.

 

[jon112211]

Mumps.......... wat will be the excel foemula if i want due date in seperate cell......
=IF(B2<=TODAY()+3,B2-TODAY()&" days left!","").......
i was usig this formula but it calculate due date in negative to more than -3 days.......

tell 3 formula separetely.......
1st only positive (+3) due dates should be shown others should go blank (0days left, 1 day left, 2 day left, 3 day left)..........
2nd only negative (-3) due dates should be shown others should go blank(0days left, -1 day left, -2 day left, -3 day left).........
3rd both +3 and -3 due dates should show rest should go blank (0days left, 1 day left, 2 day left, 3 day left, -1 day left, -2 day left, -3 day left)..........

all other due date must go blank

 

[mumps]

Hi jon. What do you mean by

due date in seperate cell

The due date is already in column D. Do you want to copy it into another cell? Also, I'm not sure what you mean by

positive (+3) due dates, negative (-3) due dates and both +3 and -3 due dates

 

[jon112211]

mumps...... sorry.... u r right.... due date is already in column D...
i want to calculate the "days left"...somtig like below mentioned

column D	Column E	Column F	Column G
Due Date <tbody> </tbody>	Days LEFT (+3) <tbody> </tbody>	DAYS LAPSED (-3) <tbody> </tbody>	Days LEFT (+3) & (-3) <tbody> </tbody>
4/18/2013 <tbody> </tbody>
4/19/2013 <tbody> </tbody>
4/20/2013 <tbody> </tbody>		-3 days left! <tbody> </tbody>	-3 days left! <tbody> </tbody>
4/21/2013 <tbody> </tbody>		-2 days left! <tbody> </tbody>	-2 days left! <tbody> </tbody>
4/22/2013 <tbody> </tbody>		-1 days left! <tbody> </tbody>	-1 days left! <tbody> </tbody>
4/23/2013 <tbody> </tbody>	0 days left! <tbody> </tbody>	0 days left! <tbody> </tbody>	0 days left! <tbody> </tbody>
4/24/2013 <tbody> </tbody>	1 days left! <tbody> </tbody>		1 days left! <tbody> </tbody>
4/25/2013 <tbody> </tbody>	2 days left! <tbody> </tbody>		2 days left! <tbody> </tbody>
4/26/2013 <tbody> </tbody>	3 days left! <tbody> </tbody>		3 days left! <tbody> </tbody>
4/27/2013 <tbody> </tbody>
4/28/2013 <tbody> </tbody>

<tbody>
</tbody>

so i want formulat for column E, F & G..... (columns left blank must be blank)

 

[mumps]

Thanks for the update. I will be very busy at work today so I will look at it as soon as I can and try to come up with something.