将时间戳添加到Excel工作表的方法 [英] Method to add timestamp to excel sheet

问题描述

我将每周一次的结业签发给我的同事.

I'm sending out a weekly completion sign-off for all of our weekly training to my co-workers.

我希望它在 h2 ， i2 或 l2 等...我确定我可以弄清楚如何将其应用于其他单元格，但我需要入门帮助.

I want it to provide a timestamp to in K2 whenever a value is populated with either true or false in either h2, i2 or l2 and so on... I'm sure I can figure out how to get it to apply to the other cells but I need help getting started.

我尝试使用 NOW()，但是每次您进行更改或打开工作簿时，它都会更新，因此我需要将其自动填充一次以进行验证.

I've tried using NOW() but every time you make a change or open the workbook it updates and I need this to be auto-populated once for verification purposes.

感谢您提供的任何帮助！

Thanks for any help you can give!

推荐答案

这需要在VBA中完成，因为正如您所说， Now()工作表函数将在以下时间自动更新重新计算工作表.

This will need to be done in VBA, because as you've stated, the Now() worksheet function will automatically update when the worksheet is recalculated.

您可以使用 Worksheet_Change()事件执行此操作.

You can use the Worksheet_Change() event to do this.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim colRng As Range
    Set colRng = Union(Me.Columns("H"), Me.Columns("I"), Me.Columns("L"))

    If Not Intersect(Target, colRng) Is Nothing and (target.value = True or _
                target.Value = false) Then
        Me.Cells(Target.Row, "K").Value = Now
    End If

End Sub

此时，只要您更新 H ， I 或 L 列中的单元格，就会更新列中的相应行K 现在将带有您的时间戳.

At this point, anytime you update a cell in columns H, I, or L, the corresponding row in column K will now have your timestamp.

如果您不熟悉VBA，那么最简单的方法是右键单击 Excel底部的工作表标签，然后单击查看代码.新窗口打开后，您需要将以上代码块粘贴到VBE中.然后，您可以简单地关闭此窗口并继续下面的突出显示部分.

If you are not familiar with VBA, then the easiest way for you to do this is to Right-Click the sheet tab at the bottom of excel, then click View Code. Once the new window opens up, you will need to paste the above code block into the VBE. Then you can simply close this window and proceed to the highlighted portion below.

您需要将其重新保存为新的工作簿格式.在工作表处于活动状态时，按 F12 ，然后在另存为类型下拉列表中，选择 Excel Macro-Enabled Workbook(* .xlsm).

You will need to resave this as a new workbook format. Press F12 while you have the worksheet active, and then in the Save as type drop-down, select Excel Macro-Enabled Workbook (*.xlsm).

这篇关于将时间戳添加到Excel工作表的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！