vba 如何在更新单元格时自动填充用户名和更新时间？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27552253/
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
How can I automatically populate user name and update time when a cell is updated?
提问by mcha
Say I have a sheet with 4 columns as the following:
假设我有一个包含 4 列的工作表，如下所示：
value1 | value2 | updated by | update time
abc | | Employee1 | 18/12/2014 18:13
| xyz | Employee2 | 18/12/2014 18:13
I want the columns "updated by" and "update time" to be automatically updated if any of the cells in Value1 or Value2 has been updated.
如果 Value1 或 Value2 中的任何单元格已更新，我希望“更新时间”和“更新时间”列自动更新。
The column updated by takes the name of the user that made the change, it can be the Windows user name or Excel username.
更新的列采用进行更改的用户名，可以是 Windows 用户名或 Excel 用户名。
The column update time takes the time when any of the cells value1 or value2 has been updated.
列更新时间采用更新任何单元格 value1 或 value2 的时间。
I have no VBA knowledge; is there any way how to achieve this result? I have been searching online, I found out how to populate the update time column via a VBA procedure but the result is not exactly what is expected, and couldn't find how to pouplate the Updated By column.
我没有 VBA 知识；有什么办法可以达到这个结果吗？我一直在网上搜索，我发现了如何通过 VBA 过程填充更新时间列，但结果并不完全符合预期，并且找不到如何填充更新者列。
The VBA code I found online to populate the time is the following:
我在网上找到的用于填充时间的 VBA 代码如下：
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
Target(1, 4) = Now
Target(1, 4).EntireColumn.AutoFit
End If
End Sub
回答by Alexander Bell
Following is the possible VBA solution to your problem:
以下是您的问题可能的 VBA 解决方案：
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Or Target.Column = 2 Then
ThisRow = Target.Row
If (ThisRow = 1) Then Exit Sub
' time stamp corresponding to cell's last update
Range("D" & ThisRow).Value = Now
' Windows level UserName | Application level UserName
Range("C" & ThisRow).Value = Environ("username") & "|" & Application.UserName
Range("C:D").EntireColumn.AutoFit
End If
End Sub
In order to protect Header Row from changes you can add code snippet shown below:
为了保护标题行免受更改，您可以添加如下所示的代码片段：
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
ThisRow = Target.Row
'protect Header row from any changes
If (ThisRow = 1) Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "Header Row is Protected."
Exit Sub
End If
If Target.Column = 1 Or Target.Column = 2 Then
' time stamp corresponding to cell's last update
Range("D" & ThisRow).Value = Now
' Windows level UserName | Application level UserName
Range("C" & ThisRow).Value = Environ("username") & "|" & Application.UserName
Range("C:D").EntireColumn.AutoFit
End If
End Sub
Hope this will help. Best regards,
希望这会有所帮助。此致，
回答by silentsurfer
Private Sub Worksheet_Change(ByVal Target As Range)
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:B")) Is Nothing Then
Cells(Target.Row, 3) = Now 'Current time
Cells(Target.Row, 4) = Application.UserName 'Excel User Name
Cells(Target.Row, 5) = Environ("UserName") 'Windows User Name
Range("C:E").EntireColumn.AutoFit
End If
End Sub
结束子
回答by mrbungle
For the windows user name you could use
对于 Windows 用户名，您可以使用
ActiveCell.Value = Environ("username")
What didn't work out for the time stamp?
什么对时间戳不起作用？
