0

I'm trying to create a spread sheet for version tracking but have a separate sheet capturing changes to the first sheet and need to include some additional information. This is the script currently running.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Log" Then Exit Sub
Application.EnableEvents = False
On Error Resume Next
With Sheets("Log").Cells(Rows.Count, 1).End(xlUp)

    .Offset(1, 0).Value = Environ("UserName")
    .Offset(1, 1) = Sh.Name
    .Offset(1, 3) = Target.Address
    .Offset(1, 4) = "'" & Target.Formula
    .Offset(1, 5) = Previous
    Previous = ""
    .Offset(1, 6) = Now
End With
Application.EnableEvents = True

End Sub

The cell that is modified is captured in column 3 as Target.Address. I would like to be able to reference the Target.Address's Row and display the value of column A on the same row from the first sheet. .

For example : Cell D7 is modified on sheet 1. Log sheet shows $D$7 as the value of the Target Address. In another cell i would like to know what the value of A7 is sense the row of the target.address was 7. It would sit in the .Offset(1, 2) = spot missing from the example as i was trying out different formulas instead of using vba.

Tried looking and having a hard time finding or wording the question properly i'm afraid.

Any suggestions would be greatly appreciated! Thank you in advance,

Dennison Coomer
  • 1

2 Answers2

1

Handling multiple changed cells:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Const LOG_SHEET As String = "Log"

    Dim c As Range

    If Sh.Name = LOG_SHEET Then Exit Sub

    For Each c In Target.Cells

        With Sheets(LOG_SHEET).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow

            .Cells(1).Value = Environ("UserName")
            .Cells(2).Value = c.EntireRow.Cells(1).Value 'server name
            .Cells(3).Value = Sh.Name
            .Cells(4) = c.Address(False, False)
            .Cells(5) = "'" & c.Value
            .Cells(6) = Now

        End With

    Next c

End Sub

You do not need to disable events here, since the "exit if log sheet" line takes care of that.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • If you don't disable events, doesn't it detect every write the script does as a change event and refire, turning into a loop? I have had issues with this in the past though not sure if it was version specific. – nbayly Jun 16 '16 at 19:01
  • Here `If Sh.Name = LOG_SHEET Then Exit Sub` short-circuits any loop which might otherwise develop. – Tim Williams Jun 16 '16 at 19:10
  • This doesnt seem to grab what im looking for either. cell2.values are left blank – Dennison Coomer Jun 16 '16 at 19:21
  • Worked for me in my testing, so not sure what the issue is in your specific workbook. – Tim Williams Jun 16 '16 at 19:56
0

You can address the changed value just using the Target.Value2 property. Your script would look like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "Log" Then Exit Sub
    Application.EnableEvents = False
    On Error Resume Next
    With Sheets("Log").Cells(Rows.Count, 1).End(xlUp)

        .Offset(1, 0).Value = Environ("UserName")
        .Offset(1, 1) = Sh.Name
        .Offset(1, 2) = Target.Value2
        .Offset(1, 3) = Target.Address
        .Offset(1, 4) = "'" & Target.Formula
        .Offset(1, 5) = Previous
        Previous = ""
        .Offset(1, 6) = Now
    End With
    Application.EnableEvents = True
End Sub
nbayly
  • 2,167
  • 2
  • 14
  • 23
  • Should keep in mind that `Target` may not always be a single cell - a user can change multiple cells at the same time, in which case Target will contain all of those cells. – Tim Williams Jun 16 '16 at 17:30
  • It's a valid note. From the description seems unlikely to be a functional step in their process. Would it make sense to detect this with `Target.Count`? Will that detect multi-area selections? – nbayly Jun 16 '16 at 17:34
  • Each change that is made is captured as a separate entry. Tracks what cell was modified, what the change was and the original cell value. Once another cell is modified another row is added with those changes. Even if its the same cell modified twice. 2 separate entries are made. – Dennison Coomer Jun 16 '16 at 17:53
  • Maybe this will help explain what I'm trying to capture more in detail: Spread sheet has a list of server names, type of change, authorized by, implemented by, etc etc. The server names never change so the log primarily captures when a type of change is modified and the date it was modified who was approved by etc. Which is working but i want to pull in the value of the server name which is in column A. So when D E F is changed the log includes the value of A on the row in which D E F were modified.. So i dont have to switch back and forth between sheets and say oh D:7 that's server XYZ – Dennison Coomer Jun 16 '16 at 17:54
  • A1=ServerA B1=Change Type C1=Employee A. User modifies Employee A to Employee B. Log sheet says User:xYZ modified $C$1 Employee B previous= Employee A. I want the script to look at the cell modified $C$1 and say Row=1 What was A1? A1=ServerA so the log would than look like User XYZ Modified ServerA $C$1 Employee B Employee A – Dennison Coomer Jun 16 '16 at 18:02
  • If you need to know the previous value then that's a little more complex: https://stackoverflow.com/questions/35018093/excel-detecting-and-keeping-track-of-value-changes-in-any-worksheet/35075434#35075434 – Tim Williams Jun 16 '16 at 18:05
  • The previous value is already working properly. All I need is some how to look at the value of the Target.Address and get the row number and then print the value of column A from the same Row. ex: IF Target.Address = $D$10 tell me what the value of $A$10 is. – Dennison Coomer Jun 16 '16 at 18:12
  • How are you handling the previous value? – Tim Williams Jun 16 '16 at 18:42
  • .Offset(1, 5) = Previous Previous = "" .Offset(1, 6) = Now – Dennison Coomer Jun 16 '16 at 19:17
  • Well I see that clearly enough, but where is `Previous` coming from ? – Tim Williams Jun 16 '16 at 22:56
  • @TimWilliams I'm not 100% sure. The script was not written by me. I'm not sure if Previous is a built in reference or what unfortunately I found the script from Mr.Excel's forums and the gentleman that posted it didn't give any credit to the original owner. – Dennison Coomer Jun 17 '16 at 12:02