Greater than >8 then MsgBbox

[Mikkel]

Hi Forum,

Might be an easy one, however Im struggeling with this and have to give in on it, so I hope theres is a friendly person who can help me.

I need to know if the value in S6:S150 is greater than 8, if the value is greater than 8 then a MsgBox with "you booked to many hrs for travelling"

Then if it is possible... can I get the time which for example was enteried into cells M6 and N6 and this was registered as to much in S6 to be "clear content" in these to cells? when clicking OK in the Msg Box.

Please find the att. excel file, to make it easier for you guys.

Thanks in advance for taking a look at it.

Mikkel

[Bob Phillips]

Is this what you mean (I have changed the code to make it simpler to change the text)

Private Sub Worksheet_Change(ByVal Target As Range)
Const msg As String = "You have chosen - "
Const msg_Guaranteed As String = "your total ammount of hours are not to exceed the guranteed hour"
Const msg1 As String = "When working Offshore you are only entitled, to book 12,00 hours a day in total"
Const msg2 As String = "When working Offshore you are only entitled, to book 8,00 hours per travel in total"
Const msg3 As String = "Please consider, to give detailed description and with an NCR number if applicable"
Const msg4 As String = "When Indirect time, check your garanteed hours for this day, " & msg_Guaranteed
Const msg5 As String = "When Indirect time, check your garanteed hours for this day, " & msg_Guaranteed
Const msg6 As String = "When Indirect time, check your garanteed hours for this day, " & msg_Guaranteed
Const msg7 As String = "When Indirect time, check your garanteed hours while at Home, " & msg_Guaranteed
Const msg8 As String = "Offshore Transport hours, are not to exceed 12,00 hours per day"

    If Intersect(Target, Range("F1:F123")) Is Nothing Then Exit Sub
    
    If Me.Cells(Target.Row, "S").Value > 8 Then
    
        Select Case Target.Value
        
            Case "Transport Hotel/Site - Site/Hotel":                       MsgBox msg & vbNewLine & Target.Value & vbNewLine & vbNewLine & msg1, vbExclamation
            Case "Travelling Onshore (home/site - site/home - site/site)":  MsgBox msg & vbNewLine & Target.Value & vbNewLine & vbNewLine & msg2, vbExclamation
            Case "Work time Onshore":                                       MsgBox msg & vbNewLine & Target.Value & vbNewLine & vbNewLine & msg3, vbExclamation
            Case "Indirect time on site Offshore":                          MsgBox msg & vbNewLine & Target.Value & vbNewLine & vbNewLine & msg4, vbExclamation
            Case "Indirect time on site Onshore":                           MsgBox msg & vbNewLine & Target.Value & vbNewLine & vbNewLine & msg5, vbExclamation
            Case "Indirect time off site Onshore (abroad)":                 MsgBox msg & vbNewLine & Target.Value & vbNewLine & vbNewLine & msg6, vbExclamation
            Case "Indirect time off site Onshore (home)":                   MsgBox msg & vbNewLine & Target.Value & vbNewLine & vbNewLine & msg7, vbExclamation
            Case "Transport Offshore":                                      MsgBox msg & vbNewLine & Target.Value & vbNewLine & vbNewLine & msg8, vbExclamation
        End Select
    End If
End Sub

[Mikkel]

Hi xld,

Thank you for cleaning up my code :-) however I dont get an MsgBox if Travelling is selected and the hrs calculated in coloumn "S" i greater than 8.

The message when selcting from the Dropdown i coloumn "F" is working perfectly.

Mikkel

[Bob Phillips]

... however I dont get an MsgBox if Travelling is selected and the hrs calculated in coloumn "S" i greater than 8.

Odd I just edited the value in your file, wit 9 hours, and I got the message.

The message when selcting from the Dropdown i coloumn "F" is working perfectly.

Sorry, I don't understand that statement.

[Mikkel]

Hi again,

Please find my excel file with your code in, if I try to book more than 8 hrs of travel, I would like a MsgBox to show up,with some kind of "service message" for the user, when there is booked more than 8 hrs. (these 8 Hrs is showing up in Cloumn "S")

If there a to many hrs booked, I would like the MsgBox to appear, with a service message and when I click "OK" to this message, to clear contents in coloumn "M" & "N" so the user have to start over. does it make sense?

maybe I didnt get all your code copied in? as I can see, se code I have is only referring to Coloumn F where I can choose different tasks!

Mikkel

[Bob Phillips]

This works on any change to M or N

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim feedback As String
    Const msg As String = "You have chosen - "
    Const msg_Guaranteed As String = "your total ammount of hours are not to exceed the guranteed hour"
    Const msg1 As String = "When working Offshore you are only entitled, to book 12,00 hours a day in total"
    Const msg2 As String = "When working Offshore you are only entitled, to book 8,00 hours per travel in total"
    Const msg3 As String = "Please consider, to give detailed description and with an NCR number if applicable"
    Const msg4 As String = "When Indirect time, check your garanteed hours for this day, " & msg_Guaranteed
    Const msg5 As String = "When Indirect time, check your garanteed hours for this day, " & msg_Guaranteed
    Const msg6 As String = "When Indirect time, check your garanteed hours for this day, " & msg_Guaranteed
    Const msg7 As String = "When Indirect time, check your garanteed hours while at Home, " & msg_Guaranteed
    Const msg8 As String = "Offshore Transport hours, are not to exceed 12,00 hours per day"
     
    If Intersect(Target, Range("M6:M123,N6:N123")) Is Nothing Then Exit Sub
     
    If Me.Cells(Target.Row, "S").Value > 8 Then
         
        Select Case Me.Cells(Target.Row, "F").Value
             
        Case "Transport Hotel/Site - Site/Hotel":                       feedback = msg1
        Case "Travelling Onshore (home/site - site/home - site/site)":  feedback = msg2
        Case "Work time Onshore":                                       feedback = msg3
        Case "Indirect time on site Offshore":                          feedback = msg4
        Case "Indirect time on site Onshore":                           feedback = msg5
        Case "Indirect time off site Onshore (abroad)":                 feedback = msg6
        Case "Indirect time off site Onshore (home)":                   feedback = msg7
        Case "Transport Offshore":                                      feedback = msg8
        End Select
        
        If feedback <> "" Then
        
            MsgBox msg & vbNewLine & Me.Cells(Target.Row, "F").Value & vbNewLine & vbNewLine & feedback, vbExclamation
            Me.Cells(Target.Row, "M").Resize(, 2).Value = ""
        End If
    End If
End Sub

[Mikkel]

Hi xld,

Thank you very much for all your help, very much appreciated..

The only issue I have left now is, when i select an Activity i coloumn "F" is the "service message" is not showing!

I only get a message when I have booked to many hrs. on travelling.

Mikkel

[Bob Phillips]

Not tested, but try this

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim feedback As String
    Const msg As String = "You have chosen - "
    Const msg_Guaranteed As String = "your total ammount of hours are not to exceed the guranteed hour"
    Const msg1 As String = "When working Offshore you are only entitled, to book 12,00 hours a day in total"
    Const msg2 As String = "When working Offshore you are only entitled, to book 8,00 hours per travel in total"
    Const msg3 As String = "Please consider, to give detailed description and with an NCR number if applicable"
    Const msg4 As String = "When Indirect time, check your garanteed hours for this day, " & msg_Guaranteed
    Const msg5 As String = "When Indirect time, check your garanteed hours for this day, " & msg_Guaranteed
    Const msg6 As String = "When Indirect time, check your garanteed hours for this day, " & msg_Guaranteed
    Const msg7 As String = "When Indirect time, check your garanteed hours while at Home, " & msg_Guaranteed
    Const msg8 As String = "Offshore Transport hours, are not to exceed 12,00 hours per day"
     
    If Intersect(Target, Me.Range("F6:F123,M6:M123,N6:N123")) Is Nothing Then Exit Sub
        
    If Me.Cells(Target.Row, "S").Value > 8 Then
         
        Select Case Me.Cells(Target.Row, "F").Value
             
        Case "Transport Hotel/Site - Site/Hotel":                       feedback = msg1
        Case "Travelling Onshore (home/site - site/home - site/site)":  feedback = msg2
        Case "Work time Onshore":                                       feedback = msg3
        Case "Indirect time on site Offshore":                          feedback = msg4
        Case "Indirect time on site Onshore":                           feedback = msg5
        Case "Indirect time off site Onshore (abroad)":                 feedback = msg6
        Case "Indirect time off site Onshore (home)":                   feedback = msg7
        Case "Transport Offshore":                                      feedback = msg8
        End Select
         
        If feedback <> "" Then
             
            MsgBox msg & vbNewLine & Me.Cells(Target.Row, "F").Value & vbNewLine & vbNewLine & feedback, vbExclamation
            Me.Cells(Target.Row, "M").Resize(, 2).Value = ""
        End If
    End If
End Sub

[Mikkel]

Hi xld,

I tried the code, the message is showing now, the only issue is when I click OK, to the message it comes back again and again and again approx 125 times, before excel then crashes!


Mikkel

[Bob Phillips]

Should have spotted that last time

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim feedback As String
    Const msg As String = "You have chosen - "
    Const msg_Guaranteed As String = "your total ammount of hours are not to exceed the guranteed hour"
    Const msg1 As String = "When working Offshore you are only entitled, to book 12,00 hours a day in total"
    Const msg2 As String = "When working Offshore you are only entitled, to book 8,00 hours per travel in total"
    Const msg3 As String = "Please consider, to give detailed description and with an NCR number if applicable"
    Const msg4 As String = "When Indirect time, check your garanteed hours for this day, " & msg_Guaranteed
    Const msg5 As String = "When Indirect time, check your garanteed hours for this day, " & msg_Guaranteed
    Const msg6 As String = "When Indirect time, check your garanteed hours for this day, " & msg_Guaranteed
    Const msg7 As String = "When Indirect time, check your garanteed hours while at Home, " & msg_Guaranteed
    Const msg8 As String = "Offshore Transport hours, are not to exceed 12,00 hours per day"
     
    On Error GoTo wsc_exit
    
    Application.EnableEvents = False
    
    If Intersect(Target, Me.Range("F6:F123,M6:M123,N6:N123")) Is Nothing Then Exit Sub
     
    If Me.Cells(Target.Row, "S").Value > 8 Then
         
        Select Case Me.Cells(Target.Row, "F").Value
             
        Case "Transport Hotel/Site - Site/Hotel":                       feedback = msg1
        Case "Travelling Onshore (home/site - site/home - site/site)":  feedback = msg2
        Case "Work time Onshore":                                       feedback = msg3
        Case "Indirect time on site Offshore":                          feedback = msg4
        Case "Indirect time on site Onshore":                           feedback = msg5
        Case "Indirect time off site Onshore (abroad)":                 feedback = msg6
        Case "Indirect time off site Onshore (home)":                   feedback = msg7
        Case "Transport Offshore":                                      feedback = msg8
        End Select
         
        If feedback <> "" Then
             
            MsgBox msg & vbNewLine & Me.Cells(Target.Row, "F").Value & vbNewLine & vbNewLine & feedback, vbExclamation
            Me.Cells(Target.Row, "M").Resize(, 2).Value = ""
        End If
    End If

wsc_exit:
    Application.EnableEvents = True
End Sub

[Mikkel]

Hi xld,

Thank you very much everything seems to work like my intensions was from the beginning.

I might come back again later, when I have other issues :-)

Thank for helping out, much appreciated.

Mikkel