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 :thumb 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