Reminder Solved/Closed

Question

Friends
Hi can anyone please help me? I need to set up a reminder in excel. I have a workbook containing a sheet that contains all our permits and inspection expiration dates for our trucking company. i would like to have a reminder telling me 5 days ahead of the due day. Also would like a pop up to show when i open that workbook. Any help will be greatly appreciated! 
Thanks for your time and efforts

vcoolio · Answer

Hello again Lynn,

Following is some code which should guide you in the right direction:-

Private Sub Workbook_Open()

Dim lr As Long
    Dim cell As Range

lr = Range("E" & Rows.Count).End(xlUp).Row

For Each cell In Range("E2:E" & lr)
    If cell.Value = [Today()] + 5 Then
    cell.Interior.ColorIndex = 6
    End If
Next

MsgBox "The licenses and permits high-lighted in yellow are due in five days' time.", vbExclamation, "WARNING!"

End Sub

The code assumes that the dates are in Column E.

The code is a Workbook_Open event and needs to be placed in the Work Book module. So, right click on the relevant sheet tab, select "view code" and in the VB Editor that opens, double click on "ThisWorkbook" (over to the left) then paste the above code. The code will execute every time that the Work Book is opened.

The above should give you something to play with and adjust to suit your actual situation.

Following is the link to my test work book for you to peruse:-

https://www.dropbox.com/s/18qpkl60wt30fv4/LynngAgain.xlsm?dl=0

I hope that this helps.

Cheerio,
vcoolio.

P.S.: In case you may want to high-light the entire row of data and have a message box pop up also, then the following may help:-


Private Sub Workbook_Open()

Dim lr As Long
    Dim lCol As Long
    Dim cell As Range

lr = Range("E" & Rows.Count).End(xlUp).Row
lCol = Cells(1, Columns.Count).End(xlToLeft).Column

For Each cell In Range("E2:E" & lr)
    If cell.Value = [Today()] + 5 Then
    Range(Cells(cell.Row, "A"), Cells(cell.Row, lCol)).Interior.ColorIndex = 6
    End If
Next

MsgBox "The licenses and permits high-lighted in yellow are due in five days' time.", vbExclamation, "WARNING!"

End Sub

https://www.dropbox.com/s/3yby5g6p1rf6vwj/LynngAgain%20%282%29.xlsm?dl=0

lynng · Answer

Thanks for your help! looks like its what i was looking for. Please is it possible to add a command button and have it reset the dates that are updated and inspected? Also i'm having an issue with some cells that are  getting high-lighted yellow wen i start entering data in the row. What do u think?...Thanks a million for your time and effort!!!!

vcoolio · Answer

Hello Lynng,

Please is it possible to add a command button and have it reset the dates that are updated and inspected? 

What exactly do you mean by "reset"?

 Also i'm having an issue with some cells that are getting high-lighted yellow when i start entering data in the row.

What is the issue? Can you please upload a sample of your work book. There may be other formulae interfering with the whole process. You can upload a sample of your work book to a free file sharing site such as DropBox, ge.tt or SpeedyShare then post the link to your file back here. Please be careful with any sensitive data.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Lynng,

The following code should do what you would like:-


Sub CheckStuff()

Dim lr As Long
        Dim cell As Range

lr = Range("F" & Rows.Count).End(xlUp).Row

For Each cell In Range("F2:F" & lr)
        If cell.Value <> vbNullString Then
        cell.EntireRow.Interior.ColorIndex = xlNone
    End If
Next

End Sub

If you have a look at your work book at the following link:-

https://www.dropbox.com/s/h7u7mrz3dv828gd/LynngAgain%283%29.xlsm?dl=0

you'll see that I have placed the above code in a standard module and assigned the code to the button on the "Reminders" sheet. I have also added Column F into the fray and headed it "Checked By". Once all high-lighted rows have been checked, type a name (or use a data validation drop down with names in it) in Column F beside the high-lighted row and click on the button. The high-light colour will disappear until the next year.

Column F will also give you a permanent record of who checked that all is in order.

I have also placed the second code from my post #1 in the Work Book module (via the "Reminders" sheet) so the permits etc. that are due in five days' time will be high-lighted once the work book is opened.

I hope that this guides you in the right direction.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Lynng,

You're welcome. I'm glad that I was able to help.

Thank you also for your kind comments about the CCM community in general.

Cheerio,
vcoolio.

Reminder

5 responses

Similar discussions

Subscribe To Our Newsletter!