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)

 

[timfritsch]

If it were me, I'd add an =IF formula in an unused column instead of VBA. Something like =IF(I1>=0.5,"Needs rest","ok"). The 0.5 is the numerical representation of 12:00:00 PM.


Tim

 

[KJ2607]

Hi Tim

Thanks for the formula - I've already used conditional formatting to highlight the cell with a time less than 12.00PM red, but the people intending to use the spreadsheet want something to pop up - is there anyway for me to use data validation in an unused to column to create a pop up based on the data in another cell (in this case I7?)

Thanks

 

[lexxie2013]

Doesn't data validation on column I with an error alert work ? Can't see why conditional formatting would interfere with it, but maybe I'm not fully understanding your requirement.

 

[KJ2607]

That was my first thinking too lexxie2013 but since Column I already had a formula and is in time format Data Validation doesn't work - hence trying the VBA, however if there is anyway to do this without using VBA I'm interested to see it

 

[lexxie2013]

I think it's possible without code. At least it appears to be working for me. If you put the data validation on the two times rather than the calculated cell. So, for instance, if the start time is in A1 and the end time is in B1, select both cells and goto data validation. Choose Allow: custom and in the formula box put :

=OR((B1-A1)>0.5,A1="",B1="")

and add an alert message of your choice. This gives an alert if the time difference is less than 12 hours. This also allows either, or both, times to be blank.

 

[KJ2607]

ok....I like your idea so can we try this with my actual columns etc?

This is what I have:

[TABLE="width: 208"]
<colgroup><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Total Shift Hours[/TD]
[TD]Total Rest Hours[/TD]
[TD]Start[/TD]
[/TR]
[TR]
[TD="align: right"]09:00[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]08:00[/TD]
[TD="align: right"]10:00[/TD]
[TD="align: right"]03:00[/TD]
[/TR]
</tbody>[/TABLE]

F7 = start time
G7 = end time
I7 = Total rest hours (J7-G7)
J7 = start time of next shift

The Actual Formula in I7 is:

=IF(ISBLANK(F7), "",IF(J7="","",MOD(J7-G7,1)))

 

[lexxie2013]

Do you just want this time difference checked when date/time is entered in column J ?

 

[lexxie2013]

If so, I would just add data validation to J7. Choose Allow : custom and enter the formula :

=(I17-F17)>0.5

and add an alert message of your choice. Let me know how you get on.

 

[KJ2607]

Yes, basically the employees need to have at least 12 hours rest between shifts so if there isn't a time in column J then column I would remain blank