none
VBA - Storing username with datetime details who edited sheet1 - also required which row or column updated RRS feed

  • Question

  • Hi,

    As I place the file on the network and I am storing username with date and time who has edited the Sheet-1. These details I am keeping in Sheet-2.

    Apart from I am interested to store which row/column has been edit. Before what data was in row/column and after update what is inside row/column.

    Following I used the code :

    Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    
    Dim LR As Long
          With Sheets("Sheet2")
              LR = .Range("A" & Rows.Count).End(xlUp).Row
              .Range("A" & LR + 1).Value = Time
              .Range("B" & LR + 1).Value = Environ("UserName")
          End With
          
    End Sub

    Thanks

    Best Regards

    Kashif Chotu

    Monday, October 28, 2013 6:44 PM

Answers

  • The following may point you in the right direction but it has limitations in that it only logs changes to individual single cells that are changed. It does not test for multiple cells and does not test with Row or Column Insert and neither does it test for multiple cell Copy and Paste.

    I have not tested but with multiple cell Copy and Paste you could look at using arrays to save the ranges much the same as the example does with the individual cell.

    Note that the Workbook_SheetChange event is used so that each change is saved as it is done.

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        'Do not process for change log sheet or if more than one cell in target
        If Sh.Name <> "Sheet2" And Target.Cells.Count = 1 Then
            On Error GoTo ReEnableEvents
            Application.EnableEvents = False
            Dim varTargNewValue
            Dim varTargOldValue
            Dim LR As Long
           
            varTargNewValue = Target.Value  'Save the change
            Application.Undo    'Undo the change
           
            varTargOldValue = Target.Value  'Save the old value
           
            Target.Value = varTargNewValue  'Re-enter the new value
           
                With Sheets("Sheet2")
                    LR = .Range("A" & Rows.Count).End(xlUp).Row
                    .Range("A" & LR + 1).Value = Time
                    .Range("B" & LR + 1).Value = Environ("UserName")
                    .Range("C" & LR + 1).Value = Sh.Name
                    .Range("D" & LR + 1).Value = Target.Address(0, 0)
                    .Range("E" & LR + 1).Value = varTargOldValue
                    .Range("F" & LR + 1).Value = varTargNewValue
                End With
        End If
       
    ReEnableEvents:
        Application.EnableEvents = True
    End Sub


    Regards, OssieMac

    • Marked as answer by Kashif Chotu Wednesday, October 30, 2013 5:36 AM
    Tuesday, October 29, 2013 1:20 AM

All replies

  • The following may point you in the right direction but it has limitations in that it only logs changes to individual single cells that are changed. It does not test for multiple cells and does not test with Row or Column Insert and neither does it test for multiple cell Copy and Paste.

    I have not tested but with multiple cell Copy and Paste you could look at using arrays to save the ranges much the same as the example does with the individual cell.

    Note that the Workbook_SheetChange event is used so that each change is saved as it is done.

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        'Do not process for change log sheet or if more than one cell in target
        If Sh.Name <> "Sheet2" And Target.Cells.Count = 1 Then
            On Error GoTo ReEnableEvents
            Application.EnableEvents = False
            Dim varTargNewValue
            Dim varTargOldValue
            Dim LR As Long
           
            varTargNewValue = Target.Value  'Save the change
            Application.Undo    'Undo the change
           
            varTargOldValue = Target.Value  'Save the old value
           
            Target.Value = varTargNewValue  'Re-enter the new value
           
                With Sheets("Sheet2")
                    LR = .Range("A" & Rows.Count).End(xlUp).Row
                    .Range("A" & LR + 1).Value = Time
                    .Range("B" & LR + 1).Value = Environ("UserName")
                    .Range("C" & LR + 1).Value = Sh.Name
                    .Range("D" & LR + 1).Value = Target.Address(0, 0)
                    .Range("E" & LR + 1).Value = varTargOldValue
                    .Range("F" & LR + 1).Value = varTargNewValue
                End With
        End If
       
    ReEnableEvents:
        Application.EnableEvents = True
    End Sub


    Regards, OssieMac

    • Marked as answer by Kashif Chotu Wednesday, October 30, 2013 5:36 AM
    Tuesday, October 29, 2013 1:20 AM
  • Excellent solution to my requirements.

    Thanks again.

    Best Regards

    Kashif Chotu

    Wednesday, October 30, 2013 5:38 AM