Not quite sure how to go about this reminder system

[erutherford]

Looking for ideas on how to go about this reminder system I am trying to create.

The form has a combobx that list all of the cars that are in the system.
Each car has a max of 6 reminders.
Each reminder has a chkbx (turning the reminder on/off), textbox for description, combox bx for number days before popup msgbox says "due".

The worksheet is setup for each car has a row.
Col A = Car Name
Col B = Expiration Date
Col C = Status (TRUE or FALSE)
Col D = Alert Date (number of days before Expiration Date)

I was going to have a popup for each date that has "expired", but then I might have a bunch of popup at one time. Is there a way to have expired dates as line items in a listbox or textbox. Sort of like a list all in one place?

looking for a strategy, concept OR better yet a sample of something close.

thanks

 

[FatBoyClam]

What triggers the check for expired items?

Are you alerting on the Alert Date having passed, or the Expiration Date?

The simplest option to me looks to be create a string where each car you want to alert about is separated by a newline

Dim cCar As Range, sh As Worksheet, strAlert As String, dtTest As Date

Set sh = ThisWorkbook.Worksheets("CarList")

Set cCar = sh.Cells(1, 1)

Do Until cCar.Value = ""
    dtTest = cCar.Offset(0, 1) 'Change to cCar.Offset(0,3) if you want the alert date used, rather than Expiration date
    
    If dtTest < Date And cCar.Offset(0, 2).Value Then
        strAlert = strAlert & cCar.Value & " expired on " & Format(dtTest, "yyyy-mm-dd") & vbCrLf 'vbCrLf puts a newline (Carriage Return, Line Feed) in
    End If

    Set cCar = cCar.Offset(1, 0)
Loop

If strAlert = "" Then strAlert = "No expired cars found"

MsgBox strAlert

 

[erutherford]

Wow thanks for the example!!!!

What triggers the check for expired items? - The due date + Alert days. TextBox1=the due date, then a checkbox to select the number of dates before the due date.

 

[FatBoyClam]

OK - So Due date is a date, and Alert Dates is a number. An example row might be like this;

The due date is 20th Feb, the Alter Dates is 30.

This will include the Astra every time the routine is run from the 21st Jan (30 days before the due date) onwards;

Dim cCar As Range, sh As Worksheet, strAlert As String, dtTest As Date

Set sh = ThisWorkbook.Worksheets("CarList")

Set cCar = sh.Cells(1, 1)

Do Until cCar.Value = ""
    dtTest = cCar.Offset(0, 1) 
    dtTest = dateadd("d",cCar.Offset(0,3)*-1,dtTest)

    If dtTest <= Date And cCar.Offset(0, 2).Value Then
        strAlert = strAlert & cCar.Value & " expired on " & Format(dtTest, "yyyy-mm-dd") & vbCrLf 'vbCrLf puts a newline (Carriage Return, Line Feed) in
    End If

    Set cCar = cCar.Offset(1, 0)
Loop

If strAlert = "" Then strAlert = "No expired cars found"

MsgBox strAlert

This will do it every day from the 21st March (30 days *after* the due date)

Dim cCar As Range, sh As Worksheet, strAlert As String, dtTest As Date

Set sh = ThisWorkbook.Worksheets("CarList")

Set cCar = sh.Cells(1, 1)

Do Until cCar.Value = ""
    dtTest = cCar.Offset(0, 1) 
    dtTest = dateadd("d",cCar.Offset(0,3),dtTest)

    If dtTest <= Date And cCar.Offset(0, 2).Value Then
        strAlert = strAlert & cCar.Value & " expired on " & Format(dtTest, "yyyy-mm-dd") & vbCrLf 'vbCrLf puts a newline (Carriage Return, Line Feed) in
    End If

    Set cCar = cCar.Offset(1, 0)
Loop

If strAlert = "" Then strAlert = "No expired cars found"

MsgBox strAlert

The difference is in the "dateadd" function - in the first version I am multiplying the alert dates by -1 (to deduct them from the due date) and in the second I am not, so as to add them.

 

[FatBoyClam]

What triggers the check for expired items? - The due date + Alert days. TextBox1=the due date, then a checkbox to select the number of dates before the due date.

I meant, what triggers the code to run, and check for expired cars?

 

[erutherford]

I haven't decided that yet. I was first thinking every time the workbook open, but I would think it should just be automatic based on the PC clock?

 

[erutherford]

Are you alerting on the Alert Date having passed, or the Expiration Date? - Alert Date Passing

The simplest option to me looks to be create a string where each car you want to alert about is separated by a newline - I agree, Simple is best.

 

[FatBoyClam]

I haven't decided that yet. I was first thinking every time the workbook open, but I would think it should just be automatic based on the PC clock?

It will not run unless the workbook is open (well - there's a route you can go down where you can use Windows scheduler to spring open excel and run the code, but doing that on a user's workstation would be a terrible thing to do), running it on opening the workbook seems reasonably sensible.

It uses the PC clock in so much as it compares the dates in the table to the current date, taken from the PC clock.

The code runs down all the items in your data table and sees whether each item has pass

In the VBA Editor, open the ThisWorkbook code module from the explorer, and select "Workbook" from the left drop-down at the top of the editor window. It should create the open and closing statements for the Workbook_Open event. You can insert the code into that routine, and it'll run when the workbook is opened. Your users will have to have Macros enabled (which is generally not the default).

You might want to change the last bit to not show an alert if nothing is found to have expired.

Cal

 

[erutherford]

I'll keep you posted on how this plays out. Can't say thanks enough for your help. Maybe someday I'll be able to contribute back to this wonderful forum!

Stay tune

 

[erutherford]

Place code in where you suggested and it causes a "type mismatch" error. I have a couple of questions and maybe I can sort it out.
Here is the current code I am working with.
Changed worksheet to where the exp dates and alert dates reside.
Error occurs at this line - "dtTest = cCar.Offset(0, 1)"

dtTest = DateAdd("d", cCar.Offset(0, 3), dtTest) - Is the "d" represent Col. D in the worksheet?
There are 2 "dtTest = ". Is this a problem?

Private Sub workbook_open()
Dim cCar As Range, sh As Worksheet, strAlert As String, dtTest As Date

Set sh = ThisWorkbook.Worksheets("Reminders")
Set cCar = sh.Cells(1, 1)

Do Until cCar.Value = ""
    dtTest = cCar.Offset(0, 1)
    dtTest = DateAdd("d", cCar.Offset(0, 3), dtTest)

    If dtTest <= Date And cCar.Offset(0, 2).Value Then
        strAlert = strAlert & cCar.Value & " expired on " & Format(dtTest, "yyyy-mm-dd") & vbCrLf 'vbCrLf puts a newline (Carriage Return, Line Feed) in
    End If

    Set cCar = cCar.Offset(1, 0)
Loop

If strAlert = "" Then strAlert = "No expired cars found"

MsgBox strAlert
End Sub