vba Excel (2003) - 在单元格上自动插入日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9109932/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel (2003) - Automatic insert date on a cell
提问by Joppe
I don't really know if this is a programming question, but I am sure one of you can easily help me with this one.
我真的不知道这是否是一个编程问题，但我相信你们中的一个人可以轻松地帮助我解决这个问题。
I am trying to create a automatic "inserted date" function inside excel. i.e. When a person inputs data in a row in my excel document I want another cell to automatically show the date of insertion.
我正在尝试在 excel 中创建一个自动的“插入日期”功能。即当一个人在我的 excel 文档中连续输入数据时，我希望另一个单元格自动显示插入日期。
Standing inside the cell i am trying to show the date, I've written the following:
站在牢房内，我试图显示日期，我写了以下内容：
=IF(ISBLANK(C20);1;TODAY())
This works great, until I open it the day after. Clearly it will set the date to "TODAY", but if I want it to only update once, at the time of the insertion - how would I do that?
这很好用，直到我第二天打开它。显然它会将日期设置为“今天”，但如果我希望它在插入时只更新一次 - 我该怎么做？
Thinking something like this (Java - pseudo).
思考这样的事情（Java - 伪）。
IF(!OTHER.CELL.ISBLANK() && THIS.CELL.ISBLANK()){
THIS.CELL = TODAY();
}
Now, how to do that in Excel?
现在，如何在 Excel 中做到这一点？
Thanks in advance.
提前致谢。
回答by brettdj
You would use the Worksheet_ChangeEvent
你会使用Worksheet_Change事件
- Right click your sheet tab
- View - Code
- Copy and Paste in the code below
- 右键单击您的工作表标签
- 查看 - 代码
- 复制并粘贴下面的代码
This code
这段代码
- tracks and change made to column C of the Activesheet
- puts in the current data and user logon name to each corresponding cell in column D
- 跟踪和更改 Activesheet 的 C 列
- 将当前数据和用户登录名放入 D 列中的每个相应单元格
Only changed column C cells are captured as specified in this line
Set rng1 = Intersect(Range("C:C"), Target)
仅按照此行中的指定捕获更改的 C 列单元格
Set rng1 = Intersect(Range("C:C"), Target)
The Application.EnableEvents = Falseis used to stop the code refiring when column D is writing to
本Application.EnableEvents = False是用来阻止时，列d被写入代码重烧
You could easily adapt this to
1) write to a different (perhaps hidden) log sheet
2) write to a text file instead
您可以轻松地将其调整为
1) 写入不同的（可能隐藏的）日志表
2) 写入文本文件
Pls let me know if you want any updates
如果您想要任何更新，请告诉我
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range
Set rng1 = Intersect(Range("C:C"), Target)
If rng1 Is Nothing Then Exit Sub
Application.EnableEvents = False
rng1.Offset(0, 1).Value = Now() & " - " & Environ("username")
Application.EnableEvents = True
End Sub
