excel 查看多个工作表更改事件

pdkcd3nj  于 12个月前  发布在  其他
关注(0)|答案(1)|浏览(120)

这里是初学者。我试图根据C列中的选择项在E列中输入一个静态时间戳,以及根据G列中的选择项在I列中输入一个静态时间戳。
如果我只尝试做一个工作表更改,这似乎是可行的-即,基于C列中的选择,在E列中的静态时间戳-但当我尝试添加另一个时,它就会崩溃。
当我删除C列中的条目时,时间戳似乎仍然存在。如果可能的话,我想删除C列条目以触发删除时间戳。我已经将我目前的工作粘贴在下面。任何帮助都将不胜感激。
工作代码(1个时间戳)

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyTableRange As Range
Dim MyDateTimeRange As Range

Set MyTableRange = Range("C8:C70")

If Intersect(Target, MyTableRange) Is Nothing Then Exit Sub

Set MyDateTimeRange = Range("E" & Target.Row)

If MyDateTimeRange.Value = "" Then

    MyDateTimeRange.Value = Now

End If

End Sub

字符串
尝试代码(2个单独的时间戳)

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyTableRange1 As Range
Dim MyDateTimeRange As Range

Set MyTableRange1 = Range("C8:C70")

If Intersect(Target, MyTableRange1) Is Nothing Then Exit Sub

Set MyDateTimeRange = Range("E" & Target.Row)

If MyDateTimeRange.Value = "" Then

    MyDateTimeRange.Value = Now

Else

Dim MyTableRange2 As Range

Set MyTableRange2 = Range("G8:G70")

If Intersect(Target, MyTableRange2) Is Nothing Then Exit Sub

Set MyDateTimeRange = Range("I" & Target.Row)

If MyDateTimeRange.Value = "" Then

    MyDateTimeRange.Value = Now

End If

End If

End Sub

oipij1gg

oipij1gg1#

这个问题是这样的:

If Intersect(Target, MyTableRange1) Is Nothing Then Exit Sub

字符串
如果你在Range("G8:G70")中改变一个单元格,你将得到Exit Sub,永远不会得到代码的后半部分。
尝试以下操作:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Intersect(Target, Me.Range("C8:C70,G8:G70"))

    If Not rng Is Nothing Then
         On Error GoTo SafeExit
         Application.EnableEvents = False

         Dim cell As Range
         For Each cell In rng
             If Not IsEmpty(cell.Value) Then
                 cell.Offset(,2).Value = Now
             Else
                 cell.Offset(,2).ClearContents
             End If
         Next
    End If

SafeExit:
    Application.EnableEvents = True
End Sub

相关问题