excel - 在 Excel 中，限制单元格范围内的字符总数(如 1000 个)

Question

我希望在一系列单元格中将我的工作簿用户限制为 1000 个字符(例如:A5:A30)。换句话说，限制 A5:A30 范围内的总字符数。到 1000 个字符。

Answer 1

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

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A5:A30")) Is Nothing Then

        Dim charCount As Long

        Dim arrValues As Variant
        arrValues = Range("A5:A30").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

        If charCount > 1000 Then
            With Application
                .EnableEvents = False
                .Undo
                .EnableEvents = True
            End With

            MsgBox "Adding this exceeds the 1000 character limit"
        End If

    End If

End Sub