- 帖子
- 1446
- 主題
- 40
- 精華
- 0
- 積分
- 1470
- 點名
- 0
- 作業系統
- Windows 7
- 軟體版本
- Excel 2010 & 2016
- 閱讀權限
- 50
- 性別
- 男
- 來自
- 台灣
- 註冊時間
- 2020-7-15
- 最後登錄
- 2024-10-21
|
回復 10# wayne0303
可能是連續觸發,可以用以下方式試試看
Application.EnableEvents = False
'會產生連續觸發的位置,例如 cell.Offset(0, -3).Value = st
Application.EnableEvents = True
觸發次數:
以下是測試觸發次數的代碼
Option Explicit
Dim N&
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xR As Range, st$, cell As Range
With Target
If .Column = 21 Then
For Each xR In .Cells
Cells(xR.Row, 1).Resize(, 22).Font.ColorIndex = 15 ^ -(Val(xR) > 1)
Next
End If
If .Column = 15 Then
For Each xR In .Cells
Cells(xR.Row, 16).Font.ColorIndex = 2 ^ -(Trim(xR) = "")
Next
End If
If .Column = 10 Then
For Each xR In .Cells
Cells(xR.Row, 10).Font.Color = RGB(0, 176, 240) ^ -(Trim(xR) = "L")
Cells(xR.Row, 10).Font.Bold = (Trim(xR) = "L")
Next
End If
If .Column = 22 Then
For Each xR In .Cells
Cells(xR.Row, 1).Resize(, 22).Font.Color = RGB(153, 102, 0) ^ -((xR Like "*拆圖"))
Next
End If
If .Column = 16 Then
For Each xR In .Cells
Cells(xR.Row, 1).Resize(, 22).Font.ColorIndex = 5 ^ -(xR.Font.Strikethrough = True)
Next
End If
N = N + 1
If .Column <> 5 Then Exit Sub
For Each cell In Union(Range([e2], [e65536].End(3)), .Cells)
st = IIf(Trim(cell) = "", "", IIf(Val(cell.Offset(0, -1)) > 1, "LOB", _
IIf(Application.WorksheetFunction.CountIf(cell, "S1A*") > 0, "TM", "MU")))
cell.Offset(0, -3).Value = st
N = N + 1
cell.Offset(0, 3).Resize(, 2) = IIf(cell <> "", 2, "")
N = N + 1
Next
End With
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MUCount&, TMCount&, cellValue, Arr, i&
Arr = Range([B2], [B65536].End(3)).Value
If IsArray(Arr) Then
MUCount = 0: TMCount = 0
For i = 1 To UBound(Arr, 1)
cellValue = Arr(i, 1)
If Not Cells(i + 1, "P").Font.Strikethrough Then
If cellValue = "MU" Then
MUCount = MUCount + 1
ElseIf cellValue = "TM" Then
TMCount = TMCount + 1
End If
End If
Next
[A1] = "案件 " & MUCount & "／" & TMCount
N = N + 1
MsgBox "合計觸發: " & N & " 次"
End If
End Sub |
|
發表於 2024-1-17 10:22
| 