问题描述
我有一个VBA宏来验证用户输入的数据(我没有使用数据验证/条件格式化目的)。我正在使用 Worksheet_Change
事件触发代码,我现在面临的问题是,当有行更改时。我不知道是否删除/插入行。
有没有区别这两个?
您可以定义范围名称,例如
RowMarker = $ A $ 1000
然后,您的更改事件上的此代码将存储此标记与其先前位置的位置,并报告任何更改(然后存储新位置)
静态lngRow As Long
Dim rng1 As Range
设置rng1 = ThisWorkbook.Names(RowMarker ).RefersToRange
如果lngRow = 0然后
lngRow = rng1.Row
退出子
结束如果
如果rng1.Row = lngRow然后退出Sub
如果rng1.Row< lngRow然后
MsgBox lngRow - rng1.Row& rows removed
Else
MsgBox rng1.Row - lngRow& rows added
End If
lngRow = rng1.Row
End Sub
I have a VBA macro that validates user entered data (I didn't use data validation/conditional formatting on purpose).
I am using Worksheet_Change
event to trigger the code, the problem I am facing now is, when there are row changes. I don't know whether it is a deleting / inserting rows.
Is there anyway to distinguish between those two?
You could define a range name such asRowMarker =$A$1000
Then this code on your change event will store the position of this marker against it's prior position, and report any change (then stores the new position)
Private Sub Worksheet_Change(ByVal Target As Range)
Static lngRow As Long
Dim rng1 As Range
Set rng1 = ThisWorkbook.Names("RowMarker").RefersToRange
If lngRow = 0 Then
lngRow = rng1.Row
Exit Sub
End If
If rng1.Row = lngRow Then Exit Sub
If rng1.Row < lngRow Then
MsgBox lngRow - rng1.Row & " rows removed"
Else
MsgBox rng1.Row - lngRow & " rows added"
End If
lngRow = rng1.Row
End Sub
这篇关于确定用户是添加还是删除行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!