Excel VBA - spreadsheet locking every time Sheet_Change event code runs

My spreadsheet is locking every time Sheet_Change event code runs when I change a cell
Excel end up failing. The two offending lines are
  Sh.Cells(6, 6) = oCountNew
   Sh.Cells(8, 6) = oCountUsed

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim oCount_New As Integer
    Dim oCount_Used As Integer
    If Target.Column = 6 And Sh.Name <> "SUMMARY" Then
        oCountUsed = Application.WorksheetFunction.CountIf(Range("F22:F1000"), "U")
        oCountNew = Application.WorksheetFunction.CountIf(Range("F22:F1000"), "N")
        Sh.Cells(6, 6) = oCountNew
        Sh.Cells(8, 6) = oCountUsed
    End If
End Sub
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
NorieConnect With a Mentor VBA ExpertCommented:
Since the code is changing cells it calls itself.

To stop that happening add this at the start,
Application.EnableEvents = False

Open in new window

and this at the end.
Application.EnableEvents = True

Open in new window

It's important that you add that at the end, if you don't no other events will be triggered after the code is finished.
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.