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.

 

[tusharm]

To learn how to do multiple tasks in a single event procedure see
Performing multiple tasks in an event procedure
http://www.tushar-mehta.com/publish_train/xl_vba_cases/1021_multiple_tasks_in_an_event_procedure.htm

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.

 

[tusharm]

You can do this w/o code. Suppose you use col. 16 as the acknowledgment column. Then, use a conditional format for column 13 where the font goes red (or whatever appeals to you) if it is now at least 30 days past the date in column 13 and column 16 is empty.

Date of registration (column 13)



I can make a new column, not a problem.


This is an example of how the DB looks like...





Thank you guys.

 

[eXtremer]

It should work immediately.

Hey Ruddles, so it works but not immediately, only after I tape something and I press TAB to go to another cell and then the message pop-up.

Another thing can the code be modified for to situations (2 different DB)...

1. To alert me when the DATE_COL hits the date I want.

2. To alert me with 30 days before the DATE_COL


And can the Notified_COL blink or became red in order to be more viewable?


Thank you for all your time.

 

[eXtremer]

Another thing can the code be modified for to situations (2 different DB)...

1. To alert me when the DATE_COL hits the date I want.

2. To alert me with 30 days before the DATE_COL

"Const ALERT_PERIOD As Integer = 30" - understood, I will change the value as I need (30, 15, 10...), still need that Cell to be more viewable

 

[Ruddles]

And can the Notified_COL blink or became red in order to be more viewable?

Not blink, but you can use conditional formatting to change the cell background and/or foreground colours if the contents are less than a certain number of days in the future. This would be the formula for ten days:-

=A1<(TODAY()+10)

 

[eXtremer]

=A1<(TODAY()+10)

Can't find the right spot where to put this code, it says "variable not defined"...

About the whole code, it doesn't work immediately.


ty.

 

[Ruddles]

Can't find the right spot where to put this code, it says "variable not defined"...

You set the conditional formatting for the cell. In 2007 it's Home > Conditional Formatting > New Rule.

About the whole code, it doesn't work immediately.

You have to decide how often you want it to run and when. See post #9 where I explained the options. As it stands, it runs wheneveranything on the worksheet changes.

 

[eXtremer]

Sorry, but you described what Options I could use, but there is no CODE to choose from.

 

[Ruddles]

The code is broadly similar for the first four options - it just needs to be placed in a different event handler depending on which event you want to trigger it. You could even have it triggered by all four events.

(I wouldn't recommend using .OnTime for this.)

It doesn't work immediately as it stands because it's currently triggered by a change to the worksheet (Worksheet_Change). If you placed the (slightly modified) code in Worksheet_Calculate it would run whenever the worksheet recalculated. Workbook_Open and Workbook_BeforeClose are other possibilities and these are also self-explanatory. I'll put something together in a few minutes and post it.

You have to decide which you prefer first, then you can place the code in the apporpriate location.

 

[Ruddles]

This seems to work fairly well: place the following in a new general code module:-

Option Explicit
 
Public Const KEY_COL As Integer = 2         [COLOR=green]' some identifying key data for the record
[/COLOR]Public Const DATE_COL As Integer = 14       [COLOR=green]' where the expiry dates are
[/COLOR]Public Const NOTIFIED_COL As Integer = 15   [COLOR=green]' where VBA puts the date the user is alerted
[/COLOR]Public Const ALERT_PERIOD As Integer = 30  [COLOR=green] ' when the warning is issued[/COLOR]
 
Public Sub CheckExpiryDates()
  Dim iLast As Long
  Dim iRow As Long
  
[COLOR=green]  ' check the expiry dates and issues a warning message
[/COLOR]  
  With ActiveSheet
    iLast = .Cells(.Rows.Count, DATE_COL).End(xlUp).Row
    For iRow = 2 To iLast
      If Not IsEmpty(.Cells(iRow, DATE_COL)) And IsEmpty(.Cells(iRow, NOTIFIED_COL)) Then
        If .Cells(iRow, DATE_COL) < Now() + ALERT_PERIOD Then
         [COLOR=green] ' if the date cell isn't empty and it's within the next ALERT_PERIOD days and we haven't
          ' already notified the user, then display a message box and set the notification date
[/COLOR]          MsgBox "Policy " & .Cells(iRow, KEY_COL) & " expires on " _
               & Format(.Cells(iRow, DATE_COL), "dd/mm/yyyy") & Space(5), _
               vbOKOnly + vbExclamation, "Policy Expiry alert!"
          Application.EnableEvents = False
          .Cells(iRow, NOTIFIED_COL) = Format(Now(), "dd/mm/yyyy")
          Application.EnableEvents = True
        End If
      End If
    Next iRow
  End With
  
End Sub

This in Worksheet_Change:-

Private Sub Worksheet_Change(ByVal Target As Range)
    
  Dim oCell As Range
  If Not Intersect(Target, ActiveSheet.Columns(DATE_COL)) Is Nothing Then
    For Each oCell In Intersect(Target, ActiveSheet.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
  
  Call CheckExpiryDates [COLOR=green]' only do this if you want this to check the dates here[/COLOR]
 
End Sub

Then you can Call CheckExpiryDates in Worksheet_Calculate, Workbook_Open, Workbook_BeforeSave, Worbook_BeforeClose - wherever you prefer it to be done.

Let me know if it doesn't work for you.