VBA最后更改方法 [英] VBA Last Change Method

问题描述

我正在寻找要在注释框中打印的功能，这些用户是更改了该单元格中数据的用户.我现在所拥有的是这样:

I am looking for a function to print in a comment box, who was the users that changed the data from that cell. What I have for now is this:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Range("A" & Target.Row).Value = "" Then GoTo EndeSub
   If Not Intersect(Range("C:JA"), Target) Is Nothing Then
     On Error GoTo EndeSub
     Application.EnableEvents = False
     Range("B" & Target.Row) = Now
   End If
EndeSub:
   Application.EnableEvents = True
 End Sub

当有人在单元格中键入内容时，它会自动触发".并且仅打印更改数据的最后一个用户名，但是我想成为某种日志，以打印所有用户.您认为有可能吗?

It "triggers" automatically when someone types something in a cell. And is printing only the last user name that changed the data, but I want to be some kind of a log, to print all the users. Do you think it is possible?

推荐答案

一种方法是，插入一个新表并将其命名为"Log"，然后像这样放置两个标题...

One way is, insert a New Sheet and name it "Log" and place the two headers like this...

在日志表上

A1->日期/时间

B1->用户

现在用此替换现有代码...

Now replace your existing code with this...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Range("A" & Target.Row).Value = "" Then GoTo EndeSub
    Dim wsLog As Worksheet
    If Not Intersect(Range("C:JA"), Target) Is Nothing Then
        On Error GoTo EndeSub
        Set wsLog = Sheets("Log")
        Application.EnableEvents = False
        Range("B" & Target.Row) = Now
        wsLog.Range("A" & Rows.Count).End(xlUp).Offset(1, 1) = Environ("UserName")
        wsLog.Range("A" & Rows.Count).End(xlUp).Offset(1) = Now
    End If
EndeSub:
   Application.EnableEvents = True
End Sub

因此，每次任何用户在目标范围内进行更改时，更改时间和用户名都会在日志表中列出.

So each time any user makes changes in the target range, the time of change and the user name will be listed on Log Sheet.

根据新设置，这些列标题应位于日志表中.

As per the new setup, these column headers should be there on the Log Sheet.

A1 --> Date/Time
B1 --> User
C1 --> Cell
D1 --> Old Value
E1 --> New Value

然后将现有代码替换为以下两个代码...

Then replace the existing code with the following two codes...

Dim oVal
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Range("A" & Target.Row).Value = "" Then GoTo EndeSub
    Dim wsLog As Worksheet
    If Not Intersect(Range("C:JA"), Target) Is Nothing Then
        On Error GoTo EndeSub
        Set wsLog = Sheets("Log")
        Application.EnableEvents = False
        Range("B" & Target.Row) = Now
        wsLog.Range("A" & Rows.Count).End(xlUp).Offset(1, 1) = Environ("UserName")
        wsLog.Range("A" & Rows.Count).End(xlUp).Offset(1, 2) = Target.Address(0, 0)
        wsLog.Range("A" & Rows.Count).End(xlUp).Offset(1, 3) = oVal
        wsLog.Range("A" & Rows.Count).End(xlUp).Offset(1, 4) = Target.Value
        wsLog.Range("A" & Rows.Count).End(xlUp).Offset(1) = Now
    End If
EndeSub:
   Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Range("C:JA"), Target) Is Nothing Then
        oVal = Target
    End If
End Sub

这篇关于VBA最后更改方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！