Excel/Modifying a Macro


Hi Jerry,

I would like to modify a macro that I am using to track changes in a workbook, but do not know the best way to go about it. The macro that I am using is listed below.
As of right now, the changes tracker is giving me the name of the active worksheet and the cell number that was changed, along with the old and new value. The problem with that is, if I add or delete any rows, it is hard to go back and trace which row was changed. So, what I wanted to do was modify the macro so that in the cell changed column it would still add the active worksheet, the cell that was changed, and the column F that corresponds to the row.

I am trying to track changes made for bank accounts. So, all of the account numbers are always in column F. So Ideally, if I changed one of the signers from John Doe to Kevin Doe, the value that would show up on the changes tracker worksheet would be, “US Accounts: $H:$7:123456489”
505 Citibank California USA Bank Account 1 123456489 USD John Doe Jane Doe

Does you know of a way to modify this so that I could get that information? Or, if you have another macro that might be able to provide this?

Here is the macro that I am currently using:

"Option Explicit

Dim vOldVal 'Must be at top of module

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

Dim bBold As Boolean

If Target.Cells.Count > 1 Then Exit Sub
If ActiveSheet.Name = "Pricing" Then Exit Sub

'On Error Resume Next

   With Application
        .ScreenUpdating = False
        .EnableEvents = False

   End With

   If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
   bBold = Target.HasFormula
       With Sheets("Changes Tracker")
         '.Unprotect Password:="Secret"
         If .Range("A1") = vbNullString Then
         .Range("A1:H1") = Array("Cell Changed", "Old Value", _
         "New Value", "Old Formula", "New Formula", "Time of Change", "Date of Change", "User")
         End If

         With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
         .Value = ActiveSheet.Name & " : " & Target.Address
         .Offset(0, 1) = vOldVal
         With .Offset(0, 2)
         If bBold = True Then
         .AddComment.Text Text:= _
         "OzGrid.com:" & Chr(10) & "" & Chr(10) & _
         "Bold values are the results of formulas"

         End If
         .Value = Target
         .Font.Bold = bBold
         End With
         .Offset(0, 3) = Time
         .Offset(0, 4) = Date
         .Offset(0, 5) = Application.UserName
         End With
         '.Protect Password:="Secret"
       End With

   vOldVal = vbNullString

   With Application
        .ScreenUpdating = True
        .EnableEvents = True
   End With
On Error GoTo 0
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
   vOldVal = Target
End Sub

Private Sub test()
   Application.EnableEvents = True
End Sub"

Thank you for your help!

I can't see your workbook, so I have no context here.   Are you still looking to track any change at all like before, but make sure the Account Number from column F is always recorded, too?

I've restructured the CHANGES TRACKER sheet so start with that sheet completely erased, then try this.   Also, I've restructured the code to make it easier for you to understand how each value is appearing in specific cells on the tracker sheet.  This should also make it simpler for you to devise and implement your own changes to the code.

Option Explicit
Dim vOldVal As String                     'Must be at top of module

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

If ActiveSheet.Name = "Pricing" Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub

    With Application
         .ScreenUpdating = False                        'speed up macro
         .EnableEvents = False                          'turn off all other macros temporarily
    End With

    If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"     'check if the cell was previously empty.
    With Sheets("Changes Tracker")
        '.Unprotect Password:="Secret"                  'unprotect the sheet
                                                        'add new set of titles if needed
        If .Range("A1") = vbNullString Then .Range("A1:F1") = _
            Array("Account", "Cell Changed", "Old Value", "New Value", "TimeStamp", "User")

        NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1    'find the next empty row
                                                            'add various values to columns on that empty row
        .Range("A" & NR).Value = Sh.Range("F" & Target.Row).Value       'add the column F account number
        .Range("B" & NR).Value = Target.Address                         'the address of the changed cell
        .Range("C" & NR).Value = vOldVal                                'the previous value
        .Range("D" & NR).Value = Target.Value                           'the new value
        .Range("E" & NR).Value = Format(Now, "h:mm:ss yyyy/mm/dd")      'a single datetimestamp
        .Range("F" & NR).Value = Application.UserName                   'the user who made the change
        .Cells.Columns.AutoFit                              'cleanup
        '.Protect Password:="Secret"                        'protect the sheet again
    End With
    vOldVal = Target.Value                                  'update the prior value to the new value, precaution

    With Application
         .ScreenUpdating = True                             'normal speed
         .EnableEvents = True                               'turn all other macros back on
    End With

End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    vOldVal = Target.Value
End Sub

Private Sub test()
    Application.EnableEvents = True
End Sub

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Jerry Beaucaire


Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files


Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files ===================== I have been offering free assistance as an Excel aid on many web sites for many years: (http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)

Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2016 About.com. All rights reserved.