vba - 工作表中有多个 Worksheet_Change

Question

我希望将我的工作簿用户限制在一系列单元格中的 1000 个字符 (Example: A5:A30) .

换句话说，限制 范围内的总字符数。 A5:A30 到 1000 个字符。

当用户填写发送范围超过 1000 个字符限制的单元格时，它将调用 Application.undo ，这应该只是删除他们添加的最后一个文本。

但是，由于我还有另一个 Private Sub Worksheet_Change(ByVal Targe As Range) 在工作表上，它会导致错误。

下面是两个 Worksheet_Change 潜艇。两者都使用相同的单元格。

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim charCount As Long

    If Not Intersect(Target, Range("E6,E11,E16")) Is Nothing Then

        Dim arrValues As Variant
        arrValues = Range("E6,E11,E16").Value2

        Dim i As Long
        Dim tempSplit As Variant
        Dim j As Long

            For i = LBound(arrValues) To UBound(arrValues)
             tempSplit = Split(arrValues(i, 1), " ")

            For j = LBound(tempSplit) To UBound(tempSplit)
                charCount = charCount + Len(tempSplit(j))
            Next j
        Next i

    End If

If charCount > 1000 Then
   Application.Undo
    MsgBox "Adding this exceeds the 1000 character limit"
 End If


            If Not Intersect(Target, Range("D6")) Is Nothing Then

        If Target.Value2 = "Material" Then
        'assumes the comment cell is one column to the right
            Target.Offset(0, 1) = "**"
        End If

    End If

            If Not Intersect(Target, Range("D7")) Is Nothing Then

        If Target.Value2 = "Material" Then
        'assumes the comment cell is one column to the right
            Target.Offset(-1, 1) = "**"
        End If

        End If

       If Not Intersect(Target, Range("D8")) Is Nothing Then

        If Target.Value2 = "Material" Then
           Target.Offset(-2, 1) = "**"
        End If

    End If
End Sub

有没有办法解决这个问题，所以我可以有两个 Worksheet_Change 在同一张工作表上？

Answer 1

你不能有两个 Worksheeet_Change一张纸上的事件。但是，一个就足够了:

Private Sub Worksheet_Change(ByVal Target As Range)

    Select Case True
    Case Not Intersect(ActiveCell, Range("A5:A30")) Is Nothing
        DoThingOne
    Case Not Intersect(ActiveCell, Range("B5:B30")) Is Nothing
        DoThingTwo
    End Select

End Sub

Private Sub DoThingOne()
    Debug.Print "THING ONE"
End Sub

Private Sub DoThingTwo()
    Debug.Print "THING TWO"
End Sub