所以我有一個動態創建的作業表（通過單擊單獨作業表上的命令按鈕生成），我試圖讓一個Worksheet_Change事件僅在該作業表中的特定范圍內觸發。我的代碼如下：
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
目的是保持對輸入到范圍內的單元格中的“S”字符數的連續計數A1:K10。我嘗試在Debug.Print陳述句之后添加If Not Intersect...陳述句，但它似乎沒有觸發，盡管目標范圍內的值被更改。我究竟做錯了什么？
uj5u.com熱心網友回復：
作業簿 SheetChange：計數和寫入
ThisWorkbook模塊
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/526177.html
標籤：擅长vba事件