Excel 2007 VBA Message Box with criteria

[KJ2607]

Hi I am VERY new to VBA and am starting to get confused!

I have a spreadsheet that monitors employees rest periods between shifts across a number of weeks I've started writing a very basic piece of code so if the employee has less than 12 hours rest, a message box will appear with a warning:

So far this is what I've done:

Sub RestHours()


If Range("I7") < #12:00:00 PM# Then


MsgBox "Candidate has had less than 12 hours rest since last shift worked. Please find another candidate to work this shift", vbOKOnly, "Unable to Use Candidate!"


Now this does work if I run the macro and then enter the data into cell I7, however I'm now stumped - How can I make it so that this message box pops up automatically when the data is entered into the cell without me have to go in and run the macro?

I also need the macro to run all the way down the I column (I7:I100) and across other columns which will eventually contain the same data i.e. M7:M100, Q7:Q100, all the way across to AT7:AT100? If its any help the columns have the same title (Total Rest Hours)

 

[KJ2607]

Hi I've entered the formula above but I keep getting a pop up stating 'The formula currently evaluates to an error. Do you want to continue?'

If I select 'Yes' It allows me to continue with the data validation, but every value that is entered into J7 now brings up an alert, even if the rest time is greater than 12 hours?

 

[lexxie2013]

That;s my fault, I had copied your data all one column to the left of yours. Data validation formula in J7 should be:

=(J7-G7)>0.5

 

[KJ2607]

Hi Lexxie

OK so we now have the validation but every value that is entered in J7 brings up the alert?

Thanks for sticking with me

 

[lexxie2013]

You're welcome. Now let's get it sorted. It's working for me. If I put 01/01/2013 09:00 in G7 and 02/01/2013 09:00 in J7 it allows it. If I change J7 to 01/01/2013 12:00 it gives the error message. Can you try those and let me know what happens ?

 

[KJ2607]

Ok I've done the same as above and it works,

But I've noticed that I don't have dates in the same cell as the times, and my times are in 24hr clock - is this causing the problem?

 

[lexxie2013]

From what I understand from your info : If somebody finishes a shift at 23:00 on 01/01/2013 and the next shift is scheduled for 07:00 on the 02/01/2013 then it should get an error, whereas 07:00 on 03/01/2013 should not. If that's correct, then, yes, the date must be included ?

 

[KJ2607]

The first part is correct - if a shift on on 01/01/2013 finishes at 23:00 and the next shift is scheduled for 07:00 on the 02/01/2013 then it would get an error as employee hasn't had 12 hours rest. However, J7 would remain blank if the employee wasn't scheduled for work on 02/01/2013 as J7 is only for that day

I would have to do the data validation for the next day in different cells, which maybe a somewhat laborious task, but worth it if I get the desired result

 

[lexxie2013]

Ok. Without dates and assuming G7 and J7 are on consecutive days, then, I believe the data validation rule :

=OR(AND(J7-G7<0,"24:00"-G7+J7>=0.5),AND(J7-G7>=0,J7-G7>=0.5))

in J7 might work. There could well be simpler formulae to do the same trick.

 

[KJ2607]

Hey Hey We have a result!!!

Thank you so much - I've literally done a happy dance at my desk!!

You are a star!

 

[lexxie2013]

You're most welcome. Very pleased we resolved it. Keep dancing !