PDA

View Full Version : Solved: Pop up data in Excel



anandbohra
07-10-2007, 04:29 AM
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.

xld
07-10-2007, 04:51 AM
It would be easier if you posted a workbook we could work with.

anandbohra
07-10-2007, 05:04 AM
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
07-10-2007, 05:07 AM
here is the attachment

xld
07-10-2007, 05:24 AM
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
07-10-2007, 05:34 AM
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.

xld
07-10-2007, 05:40 AM
100 records? You want a 100 popups? That is asking for trouble.

anandbohra
07-10-2007, 05:57 AM
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

xld
07-10-2007, 06:06 AM
Why don't you just sort the data, you can see the items easily then, and they will be grouped.

anandbohra
07-10-2007, 09:40 PM
Thanks XLD for devoting your precious time in solving my query.