Need VB code to trigger popup when any date in a range is within 30 days of system date.

Hello all, I have searched the forum for a solution and tried various options but cannot find a line of code to select dates from a range.

I have a spreadsheet that lists items of equipment that have to be calibrated or serviced regularly, the 'Due Dates' are all in one column. Can you help me with VB code to look at the entire column (i2:i100) and select any (duedate) that falls within 30 days of the system date and which will, in turn, activate my (msgbox)?

Thanks, Dave.

N.B. I forgot to define that I need the macro to run when the (workbook) is opened, also I have already created my (msgbox) which works fine.

Dave.

Hi, quick and dirty but this is the code

Please Login or Register  to view this content.

I don't know why you're post a word doc, use notepad or Excel next time!!

Modified the code a little

Please Login or Register  to view this content.

Originally Posted by enthusiastic learner

Morning Keebellah, again, thank you for your assistance and advice with my project. I have applied your suggested code and found that the message box appears even if a date on my spreadsheet is over 60 days in the future. Also, the number of 'days remaining ' in the (msgbox) do not always add up correctly.
I am new to VB code, so I do not fully understand your code. if you could add comments to the code I may be able to figure it out for myself?

That said, it is helping me see the bigger picture.

Thanks again,
Dave.

What more is there to add to the code,first of all it's your own code and second it explains itself, all you need to do is add a condition that a date in the future is ignored

Please Login or Register  to view this content.

Hi Greg, this looks like just what I need. However, I have been trying to apply it to my worksheet and cannot get past the "details" part. I get an error 'Runtime error 9, Subscript out of range'

I have been trying the 'sheet' name i,e, 'sheet14' also the tab name 'Bodyshop_Calibrations'

Do I need to give my worksheet a name in the properties box?

Dave

NB

Ok,what a **** I am. I have finally figured out the 'Name' problem and can step through the code when applied to my sheet but I still cannot get the message to appear on que. I will look again before asking for further help.

Cheers, Dave.

A NOTE for you as upcomming programmer: Remember that VBA is very 'stupid' in the sense that it does exactly what YOU tell it to do not the other way around so everything explicitly named must also exist in the same way YOU enter it in the VBA code and on the worksheets, tabs, fields, etc.
In mavy cases some actions unless explicitly not told are also case sensitive.

Use a for loop to check all sheets
What's the code you have so far?
Or do you want Greg's code to handle all sheets?

You will have to move the code to a common module and edit it accordingly but sure Greg will update

Modified Greg's code and it can be used for as many sheets as you have, condition is that Column I is always the Due date column

Edit for dynamic if you have more than 100 rows