我想区分清除单元格与更改单元格

发布于 2025-02-09 23:29:05 字数 709 浏览 2 评论 0原文

当更改单元格时，我需要帮助做一个VBA，以在同一行中添加时间戳。

这就是我所做的。

在单元格中输入一个值，下一个单元格被“ Enter”激活。我用偏移量在同一行中添加时间戳。

但是问题是，当删除单元格内容时，相同的单元格是有效的，因此时间戳在上面的一行中更新，而不是同一行。

您能帮我解决这个问题吗？

先感谢您。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rDependents As Range

On Error Resume Next
Set rDependents = Target.Dependents
If Err.Number > 0 Then
    Exit Sub
End If

If Not Application.Intersect(rDependents, Range("G9:G500")) Is Nothing Then
    Call Time_Stamp

End If
End Sub

Private Sub Time_Stamp()

ActiveCell.Offset(rowOffset:=-1, columnOffset:=2).Activate
With Selection
.Value = Now
End With

ActiveCell.Offset(rowOffset:=1, columnOffset:=-3).Activate

End Sub

原文

I need help to make a VBA to add the time stamp in the same row when a cell is changed.

This is what I made.

A value is entered in a cell, the next cell is activated by "enter". I used offset to add the time stamp in the same row.

But the issue is when the cell content is deleted, the same cell is active, so the time stamp is updated in one row above, not the same row.

Could you please help me to solve this issue?

Thank you in advance.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rDependents As Range

On Error Resume Next
Set rDependents = Target.Dependents
If Err.Number > 0 Then
    Exit Sub
End If

If Not Application.Intersect(rDependents, Range("G9:G500")) Is Nothing Then
    Call Time_Stamp

End If
End Sub

Private Sub Time_Stamp()

ActiveCell.Offset(rowOffset:=-1, columnOffset:=2).Activate
With Selection
.Value = Now
End With

ActiveCell.Offset(rowOffset:=1, columnOffset:=-3).Activate

End Sub

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

[旋木] 2025-02-16 23:29:05

使用Worksheet_change事件时，更改ActiveCell并不是一个好主意，因为ActiveCell可能是任何事物，具体取决于用户如何进入值。

如果他们点击Enter并将系统配置为下一行向下，那么ActiveCell将是下一个行，
可以更改该行为并进行ENTER进行，而不是向下
访问用户，可以通过单击一个单击一个单击电子表格中的随机单元格，IE ActiveCell可以在任何地方。
用户可以单击公式栏旁边的绿色刻度标记，并且单元选择根本不会改变。

您不知道活动单元将是什么，因此请使用activecell.offset（rowoffset：= - 1可以最终到达任何地方

。对象

If Target = "" Then
    ' do nothing, or maybe you want to clear any previously existing time stamp like this
    'Cells(Target.Row, "E").clearContents
Else
    Cells(Target.Row, "E").Value = Now()
End If

。设置更改导致实际值，而不是空白单元格

。

It's not a good idea to change the ActiveCell when using the Worksheet_Change event, because the ActiveCell could be anything, depending how the user enters the value.

if they hit Enter and the system is configured to go to the next row down, then ActiveCell will be the next row down,
it is possible to change that behaviour and Enter goes across instead of down
the user could confirm the cell entry by clicking a random cell in the spreadsheet, i.e. ActiveCell could be anywhere.
the user could click the green tick mark next to the formula bar and the cell selection does not change at all.

You cannot know what the active cell will be, so using ActiveCell.Offset(rowOffset:=-1 could end up anywhere.

Instead of the active cell, operate on the target object. You don't need to call another sub for that, you can do it right in the Change event

If Target = "" Then
    ' do nothing, or maybe you want to clear any previously existing time stamp like this
    'Cells(Target.Row, "E").clearContents
Else
    Cells(Target.Row, "E").Value = Now()
End If

Now you don't need to muck around with offset to get the row where the change happened, and the time stamp will only be set if the change resulted in a real value, not a blank cell.

If, for some reason you want to use a separate sub, just feed it the row number of Target as a parameter.

回复收藏 0 原文

~没有更多了~