在一个工作表中有多个 Worksheet_Change [英] Have more than one Worksheet_Change in a Worksheet
问题描述
我希望将我的工作簿用户限制在一系列单元格 (示例:A5:A30)
内为 1000 个字符.
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?
推荐答案
一张表中不能有两个 Worksheet_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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!