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

 

[erutherford]

I think I found the problem
dtTest = cCar.Offset(0, 1)
Set cCar = sh.Cells(1, 1)

Offsets had to be the same (0,1)

What is needed to only display those "expired Reminders" that are set to TRUE (Col E.)
Can we add the description of the reminder in the msgBox?

I like it so far!

 

[FatBoyClam]

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)"

If it throws a type mismatch at "dtTest = cCar.Offset(0, 1)" then the value of the cell is not a date. dtTest is a variable - somewhere VBA stores data. Variables usually only take data of a particular type (text, numbers, dates for example).

Can you share a screen grab of the data - I'm guessing you might have a set of headers for the columns, and the code is trying to read those as though it's a data row.

dtTest = DateAdd("d", cCar.Offset(0, 3), dtTest) - Is the "d" represent Col. D in the worksheet?

The "DateAdd" function adds a certain number of time units to a provided date value and takes 3 inputs.
The first input ("d" in this case) is the unit of time ("d" represents "days"), the second input is the amount to add (read from your Alert Dates column), and the last is the date to add it to (from your Due Date column).

There are 2 "dtTest = ". Is this a problem?

No - the first sets the value of dtTest, the second changes it (and uses the original value to calculate the new value). It could all be done in one statement, but I prefer to break things out into simple clear steps - I find it makes it easier to read, and thus troubleshoot.

If you share a screen grab of the data, I can check the assumptions I've made on how it should work, and adjust the code for you accordingly.

What is needed to only display those "expired Reminders" that are set to TRUE (Col E.)
Can we add the description of the reminder in the msgBox?

It should already be checking for the expired reminders being set to TRUE, but you said these values were in column C, so that's where it's looking.
You can have the Message box say whatever you want, yes

 

[erutherford]

Col A Col B Col C Col D Col E

Data starts in row 3

I think I have everything working fine. Was able to add the description of the reminder to the popup. It checks the TRUE/FALSE condition (my mistake of Col id).
The date that triggers the popup isn't quite right. (its return the date of 11-15-1899) We need the "R1DDate" - "R1ADate" = the date the popup should occur. (col 3 - col 4). Currently Col 3 is a DATE and Col 4 is a NUMBER


So here is the modified code so far

Private Sub workbook_open()
Dim cCar As Range, sh As Worksheet, strAlert As String, dtTest As Date, Des As Range 'DES=Description

Set sh = ThisWorkbook.Worksheets("Reminders")

Set cCar = sh.Cells(3, 1) 'Description of Vehicle
Set Des = sh.Cells(3, 2)  'Description of Reminder

Do Until cCar.Value = ""

    dtTest = cCar.Offset(3, 1) 'Starts row 3, First col.
    dtTest = DateAdd("d", cCar.Offset(0, 3) * -1, dtTest)

    If dtTest <= Date And cCar.Offset(0, 4).Value Then ' This checks TRUE/FALSE
        strAlert = strAlert & cCar.Value & " " & Des.Value & " expired on " & Format(dtTest, "mm-dd-yyyy") & 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 reminders found"

MsgBox strAlert
End Sub

 

[erutherford]

Well I thought I had it dialed in, but put 20 reminders in and it throws the Mistype error! The MsgBox pops up with two reminders, but no more. Back to square one

 

[erutherford]

Cal,
I recreated a worksheet exactly as you presented along with the code. Works as it should, except the year comes up as "1900". I'll make incremental changes and see if I can modify it to match my worksheet.
Any idea on the "year" issue.

Thanks for hanging in there with me, really appreciate it.

 

[FatBoyClam]

Hi,

In the code you've posted, you look to be picking up the wrong date.

Set cCar = sh.Cells(3, 1) 'Description of Vehicle

This set the cCar range variable to represent cell A3 (row 3, column 1)

    dtTest = cCar.Offset(3, 1) 'Starts row 3, First col.

The "Offset" method of the cCar range object represents a cell that is 3 rows down and one column over from the cell represented by cCar

In the first run this means you'll be looking for the date in cell B6 (3 down and 1 over from A3).

The date you were getting shown originally (november 15th 1899) is the equivalent in Excel of -45 days (Excel stores dates as a whole number starting 1/1/1900, which is effectively stored as 1. You were picking up a blank value for the due date, then deducting 45 days from it)

If you put your data back the way you had it (Car Name, Alert Description, Due Date, Alert Days, Status) your code should look like this;

Private Sub workbook_open()
Dim cCar As Range, sh As Worksheet, strAlert As String, dtTest As Date, Des As Range 'DES=Description

Set sh = ThisWorkbook.Worksheets("Reminders")

Set cCar = sh.Cells(3, 1) 'Description of Vehicle
Set Des = sh.Cells(3, 2)  'Description of Reminder

Do Until cCar.Value = "" 'loop until the first row with no car name

    dtTest = cCar.Offset(0, 2) 'Date is 2 columns over from the Car Name - Column C
    dtTest = DateAdd("d", cCar.Offset(0, 3) * -1, dtTest) 'Deduct the number of days from column D - 3 columns over from the Car name

    If dtTest <= Date And cCar.Offset(0, 4).Value Then ' This checks the adjusted date, and TRUE/FALSE
        strAlert = strAlert & cCar.Value & " " & Des.Value & " expired on " & Format(dtTest, "mm-dd-yyyy") & vbCrLf 'vbCrLf puts a newline (Carriage Return, Line Feed) in
    End If

    Set cCar = cCar.Offset(1, 0) ' Moves to the next row
Loop

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

MsgBox strAlert
End Sub

If you give that a try and let me know if the dates start coming out right...

 

[erutherford]

I can't say thanks enough for staying with me on this. Not only does it do what I want, but a learned a ton in the process! That is worth more to me than the code! Here is the final result

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) 'cCar is c=column & Cars is column description

Do Until cCar.Value = "" 'loop until the first row with no car name
    dtTest = cCar.Offset(0, 2) 'DDate is 2 columns over from the Car Name - Column C
    dtTest = DateAdd("d", cCar.Offset(0, 4) * -1, dtTest) 'Deduct the number of days from column E - 4 columns over from the Car name
        
        If dtTest <= Date And cCar.Offset(0, 3).Value Then 'This checks the adjusted date, and TRUE/FALSE (cCar.Offset(0, 3)= 3 columns over from cCar
        strAlert = strAlert & cCar.Value & " - " & cCar.Offset(0, 1) & " " & "expires on " & Format(cCar.Offset(0, 2), "mm-dd-yyyy") & " in " & cCar.Offset(0, 4) & " days " & vbCrLf 'vbCrLf puts a newline (Carriage Return, Line Feed) in
    End If

    Set cCar = cCar.Offset(1, 0) 'Moves to the next row
Loop

If strAlert = "" Then strAlert = "No reminders today!"

MsgBox strAlert
End Sub

Naming variables is sometimes like reading personal license plates, once you figure them out, they makes perfect sense!
Thanks again for the schooling!

 

[FatBoyClam]

Thanks for the feedback, hope it's all working as required now.