Track Changes via Macro

[ManSim]

Hi all,
I am brandnew to VBA but learn a bit more every day. Now I have the following problem that needs your help:
I have about 15 different excel inventories and need to check every Monday if changes were made in the last week. All files are shared hence "tracking changes" is basically possible.
Now instead of clicking the mouse so many times I attempted to start recording a macro to ease my work. The result was the following code:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Sub TrackChanges()
'
' TrackChanges Macro
'
<o> </o>
'
With ActiveWorkbook
.HighlightChangesOptions When:="15.03.2010"
.ListChangesOnNewSheet = True
.HighlightChangesOnScreen = True
End With
End Sub
<o> </o>
<o> </o>
Unfortunately this macro does not really work - nothing happens! What is wrong with this?
<o> </o>
PS. At a later stage I want to replace the hard coded date with an input msgbox...

Much appreciate any comment.
Thanks in advance

 

[arkusM]

How would one modify the code so when pasting in a range of cells, rather than show "multiple cell Select", it shows the old/new value of each individual cell?

Probably with a fair bit of effort, though I am sure it could be done. I would imagine you would need to use an array to store the data. I have never pursued this avenue and unfourtunatly I cannot help you with it at this time. If you fgure it out I would be interested in seeing the code.

 

[A Antury]

Hi arkusM, Your code is great, very useful. I was wondering whether you could help me adding three columns to the 'tracker' tab, in this case I need to list the values from columns A, B and AC on the original sheet whenever a cell has been modified (so the rows can be identified). I need this because the length of rows is quite big and I need to identify the changes by the columns A, B and AC.
Many thanks for your help.

 

[bhushanvshah]

Dear arkusM,

Your code does not work if the data is pulled from another excel file.

For example
Two excel files
Input, Output

I have following sheet in file Output: Sheet1 and Tracker
(Your code is pasted in the Output Excel File)

Data is pulled in the Sheet1 of Output (from Input file)
using =[Input.xlsm]Sheet1!B8


Whenever data is added in Input file, it is reflected in Output file,
But the changes are not reflected in the tracker sheet.

Kindly help me to solve the above.

 

[arkusM]

Hi arkusM, Your code is great, very useful. I was wondering whether you could help me adding three columns to the 'tracker' tab, in this case I need to list the values from columns A, B and AC on the original sheet whenever a cell has been modified (so the rows can be identified). I need this because the length of rows is quite big and I need to identify the changes by the columns A, B and AC.
Many thanks for your help.

Try adding the lines in red below. These will monitor only the columsn your are interested in

Option Explicit
Dim sOldAddress As String
Dim vOldValue As Variant
Dim sOldFormula As String
'---------------------------------------------------------------------------------------
' Procedure : Workbook_SheetChange
' Author    : mreierson
' Date      : 1/7/2009
' Purpose   : To track changes to any cell on a new tab
'               [URL="http://www.mrexcel.com/forum/showthread.php?t=376400&referrerid=76744"]VBA "ignoring" or exit sub with "Select All"[/URL]
'               With many thanks to Colin_L for helping me get this to work
'---------------------------------------------------------------------------------------
'
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim wSheet As Worksheet
    Dim wActSheet As Worksheet
    Dim iCol As Integer
    Set wActSheet = ActiveSheet
    'Precursor Exits
    'If ActiveSheet.Name <> "Information" Then Exit Sub '***This Allows you to set which tab to monitor, if you want
[COLOR=#FF0000]IF target.column <> 1 or  target.column <> 3 or target.column <> 29 then exit sub[/COLOR]

 If vOldValue = "" Then Exit Sub
    'Continue
    On Error Resume Next    ' This Error resume next is only to allow the creation of the tracker sheet.
    Set wSheet = Sheets("Tracker")
    '**** Add the tracker Sheet if it does not exist ****
    If wSheet Is Nothing Then
        Set wActSheet = ActiveSheet
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Tracker"
    End If
    On Error GoTo 0
    '**** End of specific error resume next
    On Error GoTo ErrorHandler
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    With Sheets("Tracker")
        '******** This bit of code moves the tracker over a column when the first columns are full**'
        If .Cells(1, 1) = "" Then                                                               '
            iCol = 1                                                                            '
        Else                                                                                    '
            iCol = .Cells(1, 256).End(xlToLeft).Column - 7                                      '
            If Not .Cells(65536, iCol) = "" Then                                                '
                iCol = .Cells(1, 256).End(xlToLeft).Column + 1                                  '
            End If                                                                              '
        End If                                                                                  '
        '********* END *****************************************************************************
        '******** Sets the Column Headers **********************************************************
        If LenB(.Cells(1, iCol).Value) = 0 Then
            .Range(.Cells(1, iCol), .Cells(1, iCol + 7)) = Array("Cell Changed", "Old Value", _
                                                                 "New Value", "Old Formula", "New Formula", "Time of Change", "Date of Change", "User")
            .Cells.Columns.AutoFit
        End If
        With .Cells(.Rows.Count, iCol).End(xlUp).Offset(1)
            .Value = sOldAddress
            .Offset(0, 1).Value = vOldValue
            .Offset(0, 3).Value = sOldFormula
            If Target.Count = 1 Then
                .Offset(0, 2).Value = Target.Value
                If Target.HasFormula Then .Offset(0, 4).Value = "'" & Target.Formula
            End If
            .Offset(0, 5) = Time
            .Offset(0, 6) = Date
            .Offset(0, 7) = Application.UserName
            .Offset(0, 7).Borders(xlEdgeRight).LineStyle = xlContinuous
        End With
        '.Protect Password:="Secret"
    End With
ErrorExit:
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    wActSheet.Activate
    Exit Sub
ErrorHandler:
    'any error handling you want
    'Debug.Print "We have an error"
    Resume ErrorExit
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    With Target
        sOldAddress = .Address(external:=True)
        If .Count > 1 Then
            vOldValue = "Multiple Cell Select"
            sOldFormula = vbNullString
        Else
            vOldValue = .Value
            sOldFormula = Cells(.Row(), 9)
            ' If .HasFormula Then
            '     sOldFormula = "'" & Target.Formula
            ' Else
            '     sOldFormula = vbNullString
            ' End If
        End If
    End With
End Sub

 

[arkusM]

Dear arkusM,

Your code does not work if the data is pulled from another excel file.

For example
Two excel files
Input, Output

I have following sheet in file Output: Sheet1 and Tracker
(Your code is pasted in the Output Excel File)

Data is pulled in the Sheet1 of Output (from Input file)
using =[Input.xlsm]Sheet1!B8


Whenever data is added in Input file, it is reflected in Output file,
But the changes are not reflected in the tracker sheet.

Kindly help me to solve the above.

bhushanvshah,

The reason this code is not tracking changes in this situation is because as far as an excel Event is concerned nothing is happening. The trraacking change code can only be triggered on an event such as typing in a value. Sorry.

Mark

 

[A Antury]

Hi arkusM, Thanks so much for your reply. What I would like to have on the tracker tab in addition to the output from your code is the values from columns A, B & AC as they are in the original sheet(s), so instead of having 7 columns in the output, there would be 10 (including the values from A, C & AC imported automatically from the original sheet). The expected column headings would be:
[TABLE="width: 977"]
<tbody>[TR]
[TD="class: xl506, width: 89"]Cell Changed[/TD]
[TD="class: xl506, width: 64"]Old Value[/TD]
[TD="class: xl506, width: 69"]New Value[/TD]
[TD="class: xl506, width: 78"]Old Formula[/TD]
[TD="class: xl506, width: 83"]New Formula[/TD]
[TD="class: xl506, width: 98"]Time of Change[/TD]
[TD="class: xl506, width: 97"]Date of Change[/TD]
[TD="class: xl506, width: 48"]User[/TD]
[TD="class: xl506, width: 115"](Value from col A)[/TD]
[TD="class: xl506, width: 112"](Value from col B)[/TD]
[TD="class: xl506, width: 124"](Value from col AC)[/TD]
[/TR]
</tbody>[/TABLE]
Thank you

 

[bhushanvshah]

bhushanvshah,

The reason this code is not tracking changes in this situation is because as far as an excel Event is concerned nothing is happening. The trraacking change code can only be triggered on an event such as typing in a value. Sorry.

Mark

Mark,
thanks for your reply. how else could this problem be addressed?
rgds
Bhushan

 

[arkusM]

Hi arkusM, Thanks so much for your reply. What I would like to have on the tracker tab in addition to the output from your code is the values from columns A, B & AC as they are in the original sheet(s), so instead of having 7 columns in the output, there would be 10 (including the values from A, C & AC imported automatically from the original sheet). The expected column headings would be:
[TABLE="width: 977"]
<tbody>[TR]
[TD="class: xl506, width: 89"]Cell Changed
[/TD]
[TD="class: xl506, width: 64"]Old Value
[/TD]
[TD="class: xl506, width: 69"]New Value
[/TD]
[TD="class: xl506, width: 78"]Old Formula
[/TD]
[TD="class: xl506, width: 83"]New Formula
[/TD]
[TD="class: xl506, width: 98"]Time of Change
[/TD]
[TD="class: xl506, width: 97"]Date of Change
[/TD]
[TD="class: xl506, width: 48"]User
[/TD]
[TD="class: xl506, width: 115"](Value from col A)
[/TD]
[TD="class: xl506, width: 112"](Value from col B)
[/TD]
[TD="class: xl506, width: 124"](Value from col AC)
[/TD]
[/TR]
</tbody>[/TABLE]
Thank you

I can try to make this work, but I am limited on time right now so as long as you are not in a hurry I will try my best.

 

[arkusM]

Mark,
thanks for your reply. how else could this problem be addressed?
rgds
Bhushan

A way that I can think of are, doing a test when the sheet first opens and tracking changes after a refresh or recalc. I believe somewhere on this tread there is code that does that for a live feed scenario, but that would not be ideal for normal worksheets because in that case it tested every few seconds which would be impractical for normal sheets.

Unfortunately I do not have the time right now to work through the code and test it for you. My apologies.

 

[A Antury]

Thank you arkusM.