Notify if the date expires in Excel DB

[eXtremer]

Hi all.

There is a excel database with columns, there is a column with dates and I need to be notified by excel somehow that it past for example 30 days from that date, is it possible to implement such a thing in excel ? I need to be notified just once, after that to click on it and the reminder has to be set OFF.

I really need to find a workaround.

Thank you in advance.

 

[eXtremer]

Okay, thanks for the response. And I'll take a look at your worksheet when I get home, as I'll be able to access the file-sharing site from there.

In the meantime, consider this: the code has to be run periodically in order to detect when the expiry dates become 'alertable'. That could be:-

• only once, when the workbook is opened (or just before it's closed)
• every time the workbook is saved
• every time someone manually modifies the contents of a cell - any cell (if this happens during normal use - which isn't always the case)
• every time the worksheet recalculates (which it won't unless there are formulae in it)
• every X seconds or minutes (using the .OnTime method - which I try to avoid)

You have to decide which of these 'triggers' is the one you want. If you have a lot of data and you do it every time you enter a value, for example, that will slow down the response of the worksheet.

I've uploaded the image to another website check this link
This DB is not so big, so I think the "only once, when the workbook is opened" will be just fine.

 

[eXtremer]

gtg now, I'll check this thread later today.

thx Ruddles

 

[eXtremer]

I've changed:

Private Sub Worksheet_Calculate()
to
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Now, when I click on any cell I get this error:

Compile Error:

Ambiguous name detected: Worksheet_Change

P.S.: Office 2003

 

[Ruddles]

You can only have one Worksheet_Change event handler per sheet. If you want to do two different things, you need to combine the code into one subroutine.

Try this:-

Option Explicit
 
Const KEY_COL As Integer = 2
Const DATE_COL As Integer = 14
Const NOTIFIED_COL As Integer = 15
Const ALERT_PERIOD As Integer = 30
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  Dim oCell As Range
  Dim ws As Worksheet
  Dim iLast As Long
  Dim iRow As Long
  
  Set ws = ActiveSheet
 
[COLOR=green]  ' the bit that checks the expiry dates
[/COLOR]  
  iLast = ws.Cells(ws.Rows.Count, DATE_COL).End(xlUp).Row
  For iRow = 2 To iLast
    If Not IsEmpty(ws.Cells(iRow, DATE_COL)) And IsEmpty(ws.Cells(iRow, NOTIFIED_COL)) Then
      If ws.Cells(iRow, DATE_COL) < Now() + ALERT_PERIOD Then
        ' if the date cell isn't empty and it's within the next 30 dayts and we haven't already
        ' notified the user, then display a message box and set the notification date
        MsgBox "Policy " & ws.Cells(iRow, KEY_COL) & " expires on " _
             & Format(ws.Cells(iRow, DATE_COL), "dd/mm/yyyy") & Space(5), _
             vbOKOnly + vbExclamation, "Policy Expiry alert!"
        ws.Cells(iRow, NOTIFIED_COL) = Format(Now(), "dd/mm/yyyy")
      End If
    End If
  Next iRow
 
[COLOR=green]  ' the bit that resets the 'notified' flag when the expiry date is reset[/COLOR]
  
  If Not Intersect(Target, ws.Columns(DATE_COL)) Is Nothing Then
    For Each oCell In Intersect(Target, ws.Columns(DATE_COL))
      ' if the user changes the expiry date, reset the notifcation flag
      Application.EnableEvents = False
      oCell.Offset(0, 1).ClearContents
      Application.EnableEvents = True
    Next oCell
  End If
  
End Sub

 

[eXtremer]

Yes, I already another code using this fucntion ?

Can you help me combine them?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Select Case Target.Column
    Case 2: Target.Value = UCase(Target.Value)
    Case 6: Target.Value = LCase(Target.Value)
    Case 8: Target.Value = UCase(Target.Value)
    Case 9: Target.Value = UCase(Target.Value)
    Case 10: Target.Value = WorksheetFunction.Proper(Target.Value)
    Case 11: Target.Value = UCase(Target.Value)
    Case 14: Target.Value = WorksheetFunction.Proper(Target.Value)
    Case 21: Target.Value = LCase(Target.Value)
    Case 24: Target.Value = WorksheetFunction.Proper(Target.Value)
End Select
Application.EnableEvents = True
End Sub

Thank you.

 

[eXtremer]

The code that you gave me above, kind of doesn't work, yesterday I've added today's date into the DATE_COL column, when opened the DB nothing happened, made a few clicks still nothing no warning pop-upped....although 21.06.11 is the expiry date.

 

[Ruddles]

Yes, I already another code using this fucntion ?

Can you help me combine them?

This should be it:-

[FONT=Courier New]Option Explicit
 
Const KEY_COL As Integer = 2
Const DATE_COL As Integer = 14
Const NOTIFIED_COL As Integer = 15
Const ALERT_PERIOD As Integer = 30
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  Dim oCell As Range
  Dim ws As Worksheet
  Dim iLast As Long
  Dim iRow As Long
  
  Set ws = ActiveSheet
 
  [COLOR=green]' the bit that checks the expiry dates
[/COLOR]  
  iLast = ws.Cells(ws.Rows.Count, DATE_COL).End(xlUp).Row
  For iRow = 2 To iLast
    If Not IsEmpty(ws.Cells(iRow, DATE_COL)) And IsEmpty(ws.Cells(iRow, NOTIFIED_COL)) Then
      If ws.Cells(iRow, DATE_COL) < Now() + ALERT_PERIOD Then
        [COLOR=green]' if the date cell isn't empty and it's within the next 30 days and we haven't already
        ' notified the user, then display a message box and set the notification date
[/COLOR]        MsgBox "Policy " & ws.Cells(iRow, KEY_COL) & " expires on " _
             & Format(ws.Cells(iRow, DATE_COL), "dd/mm/yyyy") & Space(5), _
             vbOKOnly + vbExclamation, "Policy Expiry alert!"
        ws.Cells(iRow, NOTIFIED_COL) = Format(Now(), "dd/mm/yyyy")
      End If
    End If
  Next iRow
 
[COLOR=green]  ' the bit that resets the 'notified' flag when the expiry date is reset
[/COLOR]  
  If Not Intersect(Target, ws.Columns(DATE_COL)) Is Nothing Then
    For Each oCell In Intersect(Target, ws.Columns(DATE_COL))
   [COLOR=green]   ' if the user changes the expiry date, reset the notifcation flag
[/COLOR]      Application.EnableEvents = False
      oCell.Offset(0, 1).ClearContents
      Application.EnableEvents = True
    Next oCell
  End If
  
[COLOR=green]  ' the bit you were doing already
[/COLOR]  
  If Target.Count = 1 Then
    Application.EnableEvents = False
    Select Case Target.Column
      Case 2: Target.Value = UCase(Target.Value)
      Case 6: Target.Value = LCase(Target.Value)
      Case 8: Target.Value = UCase(Target.Value)
      Case 9: Target.Value = UCase(Target.Value)
      Case 10: Target.Value = WorksheetFunction.Proper(Target.Value)
      Case 11: Target.Value = UCase(Target.Value)
      Case 14: Target.Value = WorksheetFunction.Proper(Target.Value)
      Case 21: Target.Value = LCase(Target.Value)
      Case 24: Target.Value = WorksheetFunction.Proper(Target.Value)
    End Select
    Application.EnableEvents = True
  End If
  
End Sub[/FONT]

 

[Ruddles]

The code that you gave me above, kind of doesn't work...

And macros are enabled?

In any case, this might be fixed when you correct the 'ambiguous names' problem.

 

[eXtremer]

Thank you again, added the 22.06.2011 date I'll see what happens tomorrow.

 

[Ruddles]

It should work immediately.