Worksheet change not working

[winstela]

Good Evening All

Can anyone help me with below worksheet change event as its not working. I want to be able to check when a cell is change if the row total in the range is greater than 60 then show msgbox and then clear the contents.

so if the cell that was changed was P11 and the sum total for O11 to V11 was then greater than 60 clear contents for range O11 to V11

Thanks for looking
W

If Not Intersect(Target, Range("O9:V24")) Is Nothing Then
    For Each celV In Target
        myRowV = celV.Row
  
        Application.EnableEvents = False
    
    If Application.WorksheetFunction.Sum(.Range("o" & myRowV & ":V" & myRowV)) > 60 Then
MsgBox "you cannot exceed 60 mins"
Range("o" & myRowV & ":V" & myRowV).ClearContents
      
        Application.EnableEvents = True
        End If
    Next celV
End If

 

[jmacleary]

Hi there. You have a surplus period before the Range, also you will be disabkling events and not reenabling unless the message came out. I have removed the period and moved the enableevents line: try this mod:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("O9:V24")) Is Nothing Then
    For Each celV In Target
        myRowV = celV.Row
  
        Application.EnableEvents = False
    
    If Application.WorksheetFunction.Sum(Range("o" & myRowV & ":V" & myRowV)) > 60 Then
MsgBox "you cannot exceed 60 mins"
Range("o" & myRowV & ":V" & myRowV).ClearContents
      
        End If
        Application.EnableEvents = True

    Next celV
End If
End Sub

 

[winstela]

Thanks for your quick reply,
I think that is what happened. The enable events was not set back to true .
Once I saved the file and re-opened it works perfectly
I have an enable events true on opening the workbook

Thank you again
W