跳到主要内容

当另一个单元格中的数据在Excel中更改时，如何插入当前时间戳记？

假设您收到一个工作表，该工作表需要您修改特定列中的数据。 修改工作表后，您需要插入修改后的时间戳，以让其他人知道已更改了哪些单元格。 怎么处理呢？ 本文将向您展示当另一个单元格中的数据在Excel中发生更改时插入当前时间戳记的方法，如下图所示。

当另一个单元格中的数据随VBA代码变化时插入当前时间戳记

当另一个单元格中的数据随VBA代码变化时插入当前时间戳记

如下面的屏幕截图所示，如果更改了C列中的相应单元格，则需要在E列中填充时间戳。 请执行以下操作。

1.在工作表中，您需要修改并标记时间戳，右键单击工作表选项卡，然后单击 查看代码 从右键单击菜单中。

2.然后将以下VBA代码复制并粘贴到 代码 的窗口 Microsoft Visual Basic应用程序 窗口。 看截图：

VBA代码：当另一个单元中的数据更改时，插入当前时间戳记

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20180830
Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
Dim xDPRg, xRg As Range
xCellColumn = 3
xTimeColumn = 5
xRow = Target.Row
xCol = Target.Column
If Target.Text <> "" Then
    If xCol = xCellColumn Then
       Cells(xRow, xTimeColumn) = Now()
    Else
        On Error Resume Next
        Set xDPRg = Target.Dependents
        For Each xRg In xDPRg
            If xRg.Column = xCellColumn Then
                Cells(xRg.Row, xTimeColumn) = Now()
            End If
        Next
    End If
End If
End Sub

备注：在代码中，数字3表示您将修改C列中的数据，数字5表示时间戳将填充到E列中。请根据需要进行更改。

3。 按 其他 + Q 关闭键 Microsoft Visual Basic应用程序 窗口。

从现在开始，当更改C列中的数据时，时间戳将填充到E列中的相应单元格中，如下图所示。

小费。 日期选择器。 该 插入日期 实用程序 Kutools for Excel 帮助您快速将具有指定日期格式的日期插入到选定的单元格中。
立即下载并试用！ （30 天免费试用）

相关文章:

最佳办公生产力工具

🤖 Kutools 人工智能助手：基于以下内容彻底改变数据分析： 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup  |   多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧   |  工作簿和工作表管理器   |  资源库 （自动文本）   |  日期选择器   |  合并工作表   |  加密/解密单元格   |  按列表发送电子邮件   |  超级筛选   |   特殊过滤器 （过滤粗体/斜体/删除线...）...
前 15 个工具集:  12 文本 工具 (添加文本, 删除字符，...）   |   50+ 图表 类型 (甘特图，...）   |   40+ 实用 公式 (根据生日计算年龄，...）   |   19 插入 工具 (插入二维码, 从路径插入图片，...）   |   12 转化 工具 (小写金额转大写, 货币兑换，...）   |   7 合并与拆分 工具 (高级组合行, 分裂细胞，...）   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能，体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

产品描述

Office Tab 为 Office 带来选项卡式界面，让您的工作更加轻松

  • 在Word，Excel，PowerPoint中启用选项卡式编辑和阅读，发布者，Access，Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50％，并减少数百次鼠标单击！
Comments (60)
No ratings yet. Be the first to rate!
Paolo Iannello
Paolo Iannello
This comment was minimized by the moderator on the site
Hallo!

Der VBA-Code funktioniert wunderbar. Gibt es eine Möglichkeit den Code zu erweitern?

Jetzt gerade sie der Coade wie folgt aus:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20180830
Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
Dim xDPRg, xRg As Range
xCellColumn = 10
xTimeColumn = 16
xRow = Target.Row
xCol = Target.Column
If Target.Text <> "" Then
If xCol = xCellColumn Then
Cells(xRow, xTimeColumn) = Now()
Else
On Error Resume Next
Set xDPRg = Target.Dependents
For Each xRg In xDPRg
If xRg.Column = xCellColumn Then
Cells(xRg.Row, xTimeColumn) = Now()
End If
Next
End If
End If
End Sub

Allerdings möchte ich gerne, dass ein Zeitstempel auch dann ausgegeben wird, wenn in Spalte 3, 4, 6, 8, 10 oder 13 eine Änderung erfolgt. WElche Änderung spielt keine Rolle, nur das es eine gab. Geht das?

Liebe Grüße
Paolo :-)
Reply
0
0
Huzefa
Huzefa
This comment was minimized by the moderator on the site
Hi OP,

Not sure if you are still seeing this. I am trying to update this so that the formula picks up a range. I want Col E:I to be updated with a timestamp if Col J:N update to "Completed". Do you have the code for that?

Thanks,
Huzefa A.
Reply
0
0
crystal
crystal     Huzefa
This comment was minimized by the moderator on the site
Hi Huzefa,

I don't know if I understand your question clearly. The following VBA code may help. Please give it a try.
The relationship is a 1:1 mapping between the columns. With this script, when a cell in column J is updated to "Completed", the timestamp will be inserted into the corresponding cell in column E. Similarly, updates to column K will affect column F, and so on.

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20230816
    Dim IntersectRng As Range
    Dim TargetColumn As Integer

    ' Check if the changed cell is in columns J to N
    Set IntersectRng = Application.Intersect(Target, Me.Range("J:N"))

    If Not IntersectRng Is Nothing Then
        For Each cell In IntersectRng
            ' If the changed cell's value is "Completed"
            If cell.Value = "Completed" Then
                ' Calculate the target column in E:I
                TargetColumn = cell.Column - 5
                Me.Cells(cell.Row, TargetColumn).Value = Now()
            End If
        Next cell
    End If
End Sub
Reply
Report
0
0
Jon
Jon
This comment was minimized by the moderator on the site
How would I go about running this Macro in all of the sheets in my workbook? It is only working in the first sheet.
Reply
0
0
crystal
crystal     Jon
This comment was minimized by the moderator on the site
Hi Jon,
The following VBA code can help. Please give it a try.
Note: This VBA code needs to be added to the ThisWorkbook (Code) window.
https://www.extendoffice.com/images/stories/comments/comment-picture-zxm/Thisworkbook.png
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Updated by Extendoffice 20221208
Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
Dim xDPRg, xRg As Range
xCellColumn = 3
xTimeColumn = 5
xRow = Target.Row
xCol = Target.Column
If Target.Text <> "" Then
    If xCol = xCellColumn Then
       Cells(xRow, xTimeColumn) = Now()
    Else
        On Error Resume Next
        Set xDPRg = Target.Dependents
        For Each xRg In xDPRg
            If xRg.Column = xCellColumn Then
                Cells(xRg.Row, xTimeColumn) = Now()
            End If
        Next
    End If
End If
End Sub
Reply
Report
0
0
Michael-1904
Michael-1904
This comment was minimized by the moderator on the site
Hallo !
Ich habe noch eine Frage zu dem oben angegebenen VBA-Code.
Bei meiner Tabelle befindet sich der Wert, für den der Zeitstempel erstellt werden soll in Spalte G.
Der Wert in G errechnet sich aus der Summe der Werte in den Spalten K:AA.
Die Werte in K:AA ergeben sich aus einem anderen Tabellenblatt, unter Benutzung der Zählenwenn-Funktion.
Wenn die Werte direkt in K:AA eingegeben werden aktualisiert sich der Zeitstempel wie gewünscht.
Wenn die Werte in K:AA durch "Zählenwenn" aus einem anderen Tabellenblatt aktualisiert werden, ändert sich der Zeitstempel nicht.

Hat jemand eine Lösung für mein Problem ?
Reply
Report
0
0
crystal
crystal     Michael-1904
This comment was minimized by the moderator on the site
Hello Michael Rasche,
The formula issue cannot be handled yet. Sorry for the inconvenience.
Reply
Report
0
0
Michael
Michael
This comment was minimized by the moderator on the site
Hallo,
ich möchte auch gerne in einer Tabelle einen Zeitstempel setzen, und bin bei der Suche auf diese Seite gestossen.
Ich habe alles wie oben beschrieben eingegeben, jetzt ergibt sich noch folgendes Problem:
Der Zeitstempel soll sich ändern, wenn sich der Wert in Spalte G ändert, der Wert in Spalte bildet sich aus der Summe mehrerer Zellen (z.B. K 150 - AA 150) in der Reihe.
Wenn ich einen neuen Wert direkt in die Zellen eingebe funktioniert das VBA.
Bei meiner Tabelle ändern sich die Werte in z.B. K 150 - AA 150 aber durch die "Zählenwenn" Funktion aus einem anderen Tabellenblatt, wenn sich dann die Summe erhöht, ändert sich der Zeitstempel nicht.
Hat jemand eine Lösung für mich ?
Reply
0
0
nate
nate
This comment was minimized by the moderator on the site
how do I add this on two columns? to have two timestamps for two column on input???
Reply
0
0
Gerald.First12
Gerald.First12
This comment was minimized by the moderator on the site
Hi, finally I found this post. Thankyou I Needed this. But can anyone help me, I want to change the code so that instead of tracking the changes on the current worksheet, I want the code to track the changes on selected Columns from other worksheet, then insert the timestamp to the current worksheet.
Reply
Report
0
0
Gerald
Gerald
This comment was minimized by the moderator on the site
Hi, finally i found this post. Thankyou I Needed this. But can anyone help me, I want to change the code so that instead of tracking the changes on the current worksheet, I want the code to track the changes on selected Columns from other worksheet, then insert the timestamp to the current worksheet.
Reply
0
0
Paul
Paul
This comment was minimized by the moderator on the site
Hi, This is great info, but I'm having problem. I would like to use for change in column A time stamp column B and change in column D with time stamp column E and change in column G with time stamp column H. I can not figure out. Help please.
Reply
0
0
Lina
Lina
This comment was minimized by the moderator on the site
Hi, Is there a chance you can help me? I have tried the VBA code for the timestamp:
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20180830
Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
Dim xDPRg, xRg As Range
xCellColumn = 3
xTimeColumn = 5
xRow = Target.Row
xCol = Target.Column
If Target.Text <> "" Then
If xCol = xCellColumn Then
Cells(xRow, xTimeColumn) = Now()
Else
On Error Resume Next
Set xDPRg = Target.Dependents
For Each xRg In xDPRg
If xRg.Column = xCellColumn Then
Cells(xRg.Row, xTimeColumn) = Now()
End If
Next
End If
End If
End Sub

My problem is that I have to add dates in column 22 and I need the timestamp in column 21. The above code does not work in my sheet. I have tried it with timestamp functions and formulas, but it always changes it to the date when I opened the workbook.
I also tried to work with conditional formatting that the another cell changes the colour when the cell with the timestamp is not todays date, but this does not work either. Can you let me know what I do wrong?
Reply
0
0
crystal
crystal     Lina
This comment was minimized by the moderator on the site
Hi Lina,
Do you mean manually enter dates in column 22 and then populate timestamp automatically in adjacent cells in column 21?
You just need to change the column numbers in the code to get it down. If you only want to display time, replace the NOW() with Time() in the code. See the below code.

<div data-tag="code">Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20201106
Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
Dim xDPRg, xRg As Range
xCellColumn = 22
xTimeColumn = 21
xRow = Target.Row
xCol = Target.Column
If Target.Text <> "" Then
If xCol = xCellColumn Then
Cells(xRow, xTimeColumn) = Time()
Else
On Error Resume Next
Set xDPRg = Target.Dependents
For Each xRg In xDPRg
If xRg.Column = xCellColumn Then
Cells(xRg.Row, xTimeColumn) = Time()
End If
Next
End If
End If
End Sub
Reply
Report
0
0
Lina
Lina     crystal
This comment was minimized by the moderator on the site
Thank you for your response. Yes, that's what I wanted to do. I tried the above VBA code before but for some reason it did not work, but it works fine now. Thank you for your help
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations