Help with alert box

Hi i am attempting to set up an alert box

I want it to notify me if an employee has less than 12 hours rest between shifts
The start times (E:E) and finishing times (F:F) and the rest period (G:G) all starting from row 5
with the name and details in E1 ect

Ive written the code below but it doesnt work the message either shows every time any value is entered or if I tweak it it doesnt work at all.


Private Sub Worksheet_Change(ByVal Target As Range)
If Range("G5") < 12 Then
MsgBox Range("E1") & " has worked less than 12 hours "
End If
End Sub

Thanks x

I was then planning on creating a button on the alert which says send email and would send a prewritten email, so if anyone could help me with that or where i could find the info to do it would be great cant find anything online

How do you want the code to be triggered?
Do you only need to have a message box for those that have no rest period in column G?
Do you want a message box for all employees that meet the criteria or just the first one?
What format is your start and end times in?
Is the value in column G the result of a formula? If so Worksheet_Change will not work like that.

I'm sure there is more that needs to be clarified but this is what I got for now.

Originally Posted by stnkynts

How do you want the code to be triggered?
Do you only need to have a message box for those that have no rest period in column G?
Do you want a message box for all employees that meet the criteria or just the first one?
What format is your start and end times in?
Is the value in column G the result of a formula? If so Worksheet_Change will not work like that.

I'm sure there is more that needs to be clarified but this is what I got for now.

Hi I wasnt to sure about how it was best to have it triggered. I enter the times HH:MM manually in columns E and F and have a formula for column G ( general format) to calculate how many hours rest they have. Is it possible to trigger this buy simply entering the values in E and F ?

I was going to do this for each employee but they are 7 columns apart for example:
Employee1 (columns E:K) Employee2 (columns L:R) all the way up to Employee118 (column AER:AEX)
would i need to enter each range individually

do you have any suggestions ?
Sorry if my info is a bit hopeless im just starting out
Thanks in advance

would i need to enter each range individually

It really depends on what you want to do. If you want it to trigger on a worksheet_change event where when you enter in a time into F, for example, it will look at column G and do code based upon its value then yes, you will need to enter each range individually. If you wanted to run a subroutine to look at column G and every 7 columns after that to see if the value is less than 12 you would not need to enter each range individually. The flaw to the subroutine is that it will be applied to all employees not just the one that has had time entered (well thats not entirely true but the coding gets a little time consuming for a free project).

Sorry if my info is a bit hopeless im just starting out

No worries. But keep in mind if your info is "a bit hopeless", my ability to help you is even more hopeless because I can't read your mind, or see what you are seeing, and rely entirely on your ability to communicate effectively.


Here is a quick proposal. Since the only reference for employee names I have is from the code in your first post this will be limited in direction but will hopefully demonstrate to you some capability.

Sub Example()
Dim i As Long
Dim str As String

str = ""

For i = 7 To 830 Step 7 'every 7th Column, G to AEX
    If Cells(5, i).Value < 12 And Cells(5, i).Value <> "" Then
        If str = "" Then
            str = Cells(1, i - 2).Value
        Else
            str = str & ", " & Cells(1, i - 2).Value
        End If
    End If
Next i

MsgBox ("The following employees have not had 12 hours of rest: " & str)

End Sub

Originally Posted by stnkynts

It really depends on what you want to do. If you want it to trigger on a worksheet_change event where when you enter in a time into F, for example, it will look at column G and do code based upon its value then yes, you will need to enter each range individually. If you wanted to run a subroutine to look at column G and every 7 columns after that to see if the value is less than 12 you would not need to enter each range individually. The flaw to the subroutine is that it will be applied to all employees not just the one that has had time entered (well thats not entirely true but the coding gets a little time consuming for a free project).



No worries. But keep in mind if your info is "a bit hopeless", my ability to help you is even more hopeless because I can't read your mind, or see what you are seeing, and rely entirely on your ability to communicate effectively.


Here is a quick proposal. Since the only reference for employee names I have is from the code in your first post this will be limited in direction but will hopefully demonstrate to you some capability.

Sub Example()
Dim i As Long
Dim str As String

str = ""

For i = 7 To 830 Step 7 'every 7th Column, G to AEX
    If Cells(5, i).Value < 12 And Cells(5, i).Value <> "" Then
        If str = "" Then
            str = Cells(1, i - 2).Value
        Else
            str = str & ", " & Cells(1, i - 2).Value
        End If
    End If
Next i

MsgBox ("The following employees have not had 12 hours of rest: " & str)

End Sub

Thank you so much but I couldnt get it to work, think i would need to enter each range individually for each employee using a worksheet_change which i know would be time consuming

I have tried this :
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Target.Parent.Range("G5:G1000")
If Target.Count > 1 Then Exit Sub
If Intersect(Target, rng) Is Nothing Then Exit Sub
Application.EnableEvents = False
Select Case Target.Value
Case Is < 12
MsgBox (Range("E1") + " :less than 12 hours rest"), vbCritical + vbOKOnly, "Value Too Low"

Case Else
Target.Offset(, -1).Value = Target.Offset(, -1).Value + Target.Value
End Select
Application.EnableEvents = True
End Sub

which works if i enter the value in manually in column G but as the value is a result of a formula from E and F is there anyway i could alter the code to make it work with the function ?

Thanks again