Consulting

Results 1 to 10 of 10

Thread: Solved: Pop up data in Excel

  1. #1
    VBAX Mentor anandbohra's Avatar
    Joined
    May 2007
    Location
    Mumbai
    Posts
    313
    Location

    Solved: Pop up data in Excel

    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.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    It would be easier if you posted a workbook we could work with.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Mentor anandbohra's Avatar
    Joined
    May 2007
    Location
    Mumbai
    Posts
    313
    Location
    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

  4. #4
    VBAX Mentor anandbohra's Avatar
    Joined
    May 2007
    Location
    Mumbai
    Posts
    313
    Location
    here is the attachment

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    Put this code in ThisWorkbook code module

    [vba]

    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
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    VBAX Mentor anandbohra's Avatar
    Joined
    May 2007
    Location
    Mumbai
    Posts
    313
    Location
    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.

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    100 records? You want a 100 popups? That is asking for trouble.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    VBAX Mentor anandbohra's Avatar
    Joined
    May 2007
    Location
    Mumbai
    Posts
    313
    Location
    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

  9. #9
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    Why don't you just sort the data, you can see the items easily then, and they will be grouped.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  10. #10
    VBAX Mentor anandbohra's Avatar
    Joined
    May 2007
    Location
    Mumbai
    Posts
    313
    Location
    Thanks XLD for devoting your precious time in solving my query.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •