Solved: Pop up data in Excel

[anandbohra]

Dear All

I am having one Excel sheet containing my companys Fixed Deposit (FD) maturing date with other information.
Pl help me by providing a VBA code which gives me pop up at the start of that file that so & so FD are maturing in next 5 days time.

hope u r clear with my query.

[Bob Phillips]

It would be easier if you posted a workbook we could work with.

[anandbohra]

here is small example

my data is

Bank Amount
Maturity





ICICI 1,500,000
07-Oct-07





BNP Paribas 7,000,000
14-Jul-07





HDFC 19,000,000
12-Jul-07






now in the given data as per my query HDFC with amt 19 million & BNP Paribas with 7 Million should pop up as these are due in next 5 days

[anandbohra]

here is the attachment

[Bob Phillips]

Put this code in ThisWorkbook code module



Private Sub Workbook_Open()
Dim iLastRow As Long
Dim i As Long
Dim sMsg As String

With Worksheets("Sheet1")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To iLastRow
If .Cells(i, "C").Value >= Date And _
.Cells(i, "C").Value <= Date + 5 Then
sMsg = sMsg & vbTab & .Cells(i, "A").Value & ", " & _
.Cells(i, "B").Text & ", " & _
.Cells(i, "C").Text & vbNewLine
End If
Next i
If sMsg <> "" Then
sMsg = "Following FDs are due within 5 days:" & vbNewLine & sMsg
MsgBox sMsg
End If
End With
End Sub

[anandbohra]

Thanks XLD

code is fine

pl can u also give me code which give message boxes one by one instead of combine message coz it will not be possible to check all if the data is say 100 records then single message box will not be proper for all the records.

[Bob Phillips]

100 records? You want a 100 popups? That is asking for trouble.

[anandbohra]

this i want for my knowledge only

pl provide.

& can we group them say Maturing in next 1 Day so & so records.
next pop up saying Maturing in next 2 Days so & so records.
& so on & this will give only 5 popups.

pl help

[Bob Phillips]

Why don't you just sort the data, you can see the items easily then, and they will be grouped.

[anandbohra]

Thanks XLD for devoting your precious time in solving my query.