一个工作表中有多个Worksheet_Change

问题描述

我希望将我的工作簿用户的单元格范围限制为 (例如A5:A30) .

I am looking to limit my workbook users to 1000 characters over a range of cells (Example: A5:A30).

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

In other words limit the total characters in the range A5:A30 to 1000 characters.

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

When a user fills in a cell that sends the range over the 1000 character limit, it will call Application.undo which should just remove the last text that they added.

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

However since I have another Private Sub Worksheet_Change(ByVal Targe As Range) on the worksheet, it causes a bug.

下面是两个Worksheet_Change子项.两者都使用相同的单元格.

Below is both Worksheet_Change subs. Both use the same cells.

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 ?

Is there a way around this so I can have two Worksheet_Change on the same worksheet?

推荐答案

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

You cannot have two Worksheeet_Change events in one sheet. But, one is quite enough:

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

这篇关于一个工作表中有多个Worksheet_Change的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！