VBA - If cell value exceeds a certain number I would like a message box to appear

[Simbo2012]

Hi all,

Currently trying to put together a bit of code that returns a message box if a cell exceeds a certain number.

Here it is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
If Range("C8").Value > 10249 Then
MsgBox ("The last batch number in this range has been reached. Please complete the following steps:" & vbCrLf & _
"- Delete the last line of information entered" & vbCrLf & _
"- Open the associated Batch Numbers spreadsheet" & vbCrLf & _
"- Manually delete the dates and job numbers currently against the batch numbers")
ElseIf Range("C9").Value > 10249 Then
MsgBox ("The last batch number in this range has been reached. Please complete the following steps:" & vbCrLf & _
"- Delete the last line of information entered" & vbCrLf & _
"- Open the associated Batch Numbers spreadsheet" & vbCrLf & _
"- Manually delete the dates and job numbers currently against the batch numbers")
End If
End Sub

However no message box is appearing if the cells hit 10250. What am I doing wrong?

(I am also trying to achieve line breaks in the message box).

 

[hiker95]

Simbo2012,

Your code does not even get a chance to run, see the BOLD line:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
If Range("C8").Value > 10249 Then

'the rest of your code follows.....

Try the following:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' hiker95, 01/22/2014, ME752093
If Intersect(Target, Range("C8:C9")) Is Nothing Then Exit Sub
If Target.Value >= 10249 Then
  MsgBox ("The last batch number in this range has been reached. Please complete the following steps:" & vbCrLf & _
    "- Delete the last line of information entered" & vbCrLf & _
    "- Open the associated Batch Numbers spreadsheet" & vbCrLf & _
    "- Manually delete the dates and job numbers currently against the batch numbers")
End If
End Sub

Remember, the same worksheet can only have one Worksheet_SelectionChange event.