Don't run Worksheet_Change Event when a zero value is entered?

[Ironman]

Hi

The below code in the Worksheet_Change event triggers message boxes when any value is entered, including a zero.

I appreciate it fires when there's a change in the worksheet, but is there any way an exception can be added so the message boxes don't activate when a zero value is entered?

Many thanks!

Private Sub Worksheet_Change(ByVal Target As Range)

'If Intersect(Target, Range("EntRng")) Is Nothing Or Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("EntRng")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
'Application.EnableEvents = False
With Worksheets("Training Log")
  MsgBox "Lifetime Mileage: " & Format$(.Range("F5").Value, "#,##0") & "   " & vbNewLine & vbNewLine & _
         "Year to Date Mileage: " & Format$(.Range("C5").Value, "#,##0") & "   ", vbInformation, "Mileage Totals      "
End With


With Worksheets("Daily Tracking")
Dim uValue As Double, utext As String
    uValue = Sheets("Training Log").Range("MlsYTDLessLastYr").Value
    uValue = Round(uValue, 0)
    
Select Case uValue
        Case Is < 0
            MsgBox "You have now run " & Abs(uValue) & " miles fewer than this time last year ", vbInformation, "Mileage Compared To This Time Last Year"
            
        Case Is = 0
            MsgBox "You have now run the same number of miles as this time last year ", vbInformation, "Mileage Compared To This Time Last Year"
            
        Case Is > 0
            MsgBox "You have now run " & Abs(uValue) & " miles more than this time last year ", vbInformation, "Mileage Compared To This Time Last Year"
    End Select
End With


If Range("CurYTD").Value > Range("CurGoal").Value Then
MsgBox ("Congratulations!" & vbNewLine & "You have now run more miles this year than" & vbNewLine & vbNewLine & _
   "- The whole of " & Range("PreYear").Value & vbNewLine & _
   "- " & Range("counter").Value & " of the " & Year(Now) - 1981 & " years you've been running" & vbNewLine & _
   vbNewLine & "New rank for " & Year(Now) & " is " & Range("CurYTD").Offset(1, 0).Value & " out of " & Year(Now) - 1981), _
   vbInformation, "Another Year End Mileage Total Exceeded!     "

Range("Counter").Value = Range("Counter").Value + 1
Else
MsgBox (CLng(Range("CurGoal").Value - Range("CurYTD").Value) & _
   " miles to go until rank " & (Range("CurYTD").Offset(1, 0).Value) - 1 & " reached" & vbNewLine & vbNewLine & _
   "(Year end mileage for " & Range("PreYear").Value & ")"), _
vbInformation, "Year To Date Mileage"
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End If
Application.GoTo Sheets("Training Log").Range("A" & Rows.Count).End(xlUp).Offset(, 2)  
Application.Calculation = xlCalculationAutomatic  
End Sub

 

[GWteB]

Try replacing this part at the start of your procedure

If Target.Count > 1 Then Exit Sub

with this

If Target.Count > 1 Then Exit Sub
If Target.Value = 0 Then Exit Sub

 

[ACommandLineKindaGuy]

How about

If Target = 0 Or IsEmpty(Target) Then Exit Sub

 

[Ironman]

Many thanks gents, they both work perfectly.

I know it's a quick fix for you both, but it's been a big irritation I felt I was stuck with for 15 years, until you helped me today, so THANK YOU

 

[GWteB]

Glad we could help and thanks for the feedback