So I have a dynamically created worksheet (generated by clicking a command button on a separate sheet) and I am trying to get a Worksheet_Change event to fire only on a specific range in that worksheet. My code is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sh As Worksheet
    Set sh = ThisWorkbook.ActiveSheet

    If Not Intersect(Target, Range("A1:K10")) Is Nothing Then
        If sh.Name Like "*SP Temp*" Then
            Dim i As Variant, countOfS As Integer
            countOfS = 0
            For Each i In sh.Range("A1:K10")
                If i.Value = "S" Then
                    countOfS = countOfS   1
                End If
            Next i
            sh.Range("D12").Value = countOfS
            sh.Range("D13").Value = SCount - countOfS
                ' NOTE: SCount is a global variable set in another Sub
        End If
    End If

End Sub

The intent is to keep a running count of the number of "S" characters entered into cells in the range A1:K10. I have tried adding in Debug.Print statements after the If Not Intersect... statement, but it doesn't seem to fire, despite the values in the target range being altered. What am I doing wrong?

CodePudding user response：

A Workbook SheetChange: Count and Write

ThisWorkbook Module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    ' Invalidate.
    
    If Not Sh.Name Like "*SP Temp*" Then Exit Sub ' wrong worksheet
    
    Dim rg As Range: Set rg = Sh.Range("A1:K10")
    If Intersect(rg, Target) Is Nothing Then Exit Sub ' no intersection
            
    ' Count.
    
    Dim CountOfS As Long: CountOfS = Application.CountIf(rg, "s")
    
    ' Write
    
    ' Disable events to not re-trigger the event while writing
    ' since the same worksheet is being written to.
    Application.EnableEvents = False
        Sh.Range("D12").Value = CountOfS
        Sh.Range("D13").Value = SCount - CountOfS
    Application.EnableEvents = True
            
End Sub