Hey thanks it worked. Perfect solution Mahreen
On Sun, Jun 19, 2011 at 11:59 AM, STDEV(i) <setiyowati.d...@gmail.com>wrote: > please check this vba code, hope it helps.. > > -----Standard Module > > Sub ExpiryWarning() > ' siti Vi // Jakarta, 19 Jan 2011 > '-------------------------------- > Dim dTable As Range, t As String > Dim R As Long, N As Long, I As Integer > > Set dTable = Cells(1).CurrentRegion > N = dTable.Rows.Count > > For R = 2 To N > If IsDate(dTable(R, 5)) Then > I = dTable(R, 5) - Date > If I <= 15 Then > If I > 0 Then > dTable(R, 1).Resize(1, dTable.Columns.Count).Select > t = "Name : " & dTable(R, 2) & vbCr & vbCr > t = t & "Expiry Date : " & Format(dTable(R, 5), > "dd-mmm-yyyy") & vbCr & vbCr > t = t & "Number of days to come : " & Format(I, "0") & vbCr > MsgBox t, 48, "Warning" > End If > End If > End If > Next > Cells(1).Activate > End Sub > > -----Module Sheet1 > > Private Sub Worksheet_Activate() > ExpiryWarning > End Sub > > -----Tiisworkbook Module > > Private Sub Workbook_Open() > ExpiryWarning > End Sub > > > > On Sun, Jun 19, 2011 at 12:25 AM, Mahreen Ellahi > <mahreen.acc...@gmail.com> wrote: > > Hii > > > > I have a some data with limits expiry on different dates. I want to have > > popup window to appear whenever the limit is about to expire like 15 to > 30 > > days earlier. Can anyone help me for VBA coding. > > > > Regards > > Mahreen > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel