Report

Reminder [Solved]

Ask a question lynng 10Posts Thursday March 24, 2016Registration date July 13, 2016 Last seen - Last answered on Apr 7, 2016 03:28AM
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
See more 
Helpful
+1
moins plus
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
Add comment
Helpful
+1
moins plus
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!!!!
Add comment
Helpful
+0
moins plus
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.
lynng 10Posts Thursday March 24, 2016Registration date July 13, 2016 Last seen - Apr 5, 2016 09:04PM
Good evening!
Thanks for your time and efforts! below should be a link containing a sample of my workbook. I'm not sure the link will work because that is still new to me. Anyways if u open the workbook, in the reminders sheet i have some data in some cells. What i need to do is, if the date is within 5 days of the expiration date i want that row to be high-lighted and stay high-lighted till someone removes the high-lighted color or checks that row so its means the inspections or permits are updated. is it possible to do that with a command button? If so please help me or lead me in the right direction. Thanks for your time and efforts!!!!

https://www.dropbox.com/s/fs54ip1hteelhox/MCrecord.xlsm?dl=0
Reply
Add comment
Helpful
+0
moins plus
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.
lynng 10Posts Thursday March 24, 2016Registration date July 13, 2016 Last seen - Apr 6, 2016 11:01AM
Thanks a lot for your help!!!! That was just what i was looking for. Its an awesome site with awesome people.Thanks a million!!!!!
Reply
Add comment
Helpful
+0
moins plus
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.
Add comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!