Log Changes to Excel Workbook 1

[Tigerlili3]

I have some vba to log changes made to an Excel workbook. The changes are logged to a '.txt' file. Here is the code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Const LogFileName As String = "C:\MyFiles\Tracker.txt"
    Dim FileNum As Integer
    FileNum = FreeFile
    Open LogFileName For Append As #FileNum
    x = Sh.Name & "!" & Target.Address & " was changed by " & Application.UserName & "."
        Print #1, x
        Close #1    
End Sub

Right now, each time the contents of any cell is changed, an entry containing the sheet name, cell reference, and user name is logged to a '.txt' file. What I would like to do is only have the event log one entry per sheet per user. I don't want the cell by cell breakdown. I know I can remove the Target.Address to get rid of the cell reference, but it will still log the sheet name and user name each time a cell is changed. Is there a way to prevent it from logging more than once?

Thanks!

 

[SkipVought]

Hi,

You could use the Workbook_SheetActivate & Workbook_SheetDeactivate events, along with the Workbook_SheetChange event.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[Tigerlili3]

Thanks Skip! I was thinking that there was probably a way to incorporate other events to make it happen. I am new at using events. Can you give me an example of how to integrate the events you suggested with the one I already have?

Thanks!

 

[SkipVought]

On the Open event, Add Temp Worksheet

On each Workbook_SheetChange event, write change to temp sheet

On Close event, Open file, write file from temp sheet, close file, delete temp sheet.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[Tigerlili3]

If I do that, I will still get duplicate entries because the workbook_SheetChange event will fire each time a cell is changed. Right now, if someone changes two cells on the same sheet, the log received two entries:

Sheet1!A3 was changed by User1
Sheet1!A4 was changed by User1

If I remove Target.Address then it would read:

Sheet1! was changed by User1
Sheet1! was changed by User1

I would like to only see 'Sheet1! was changed by User1' one time.

 

[SkipVought]

So you do a pivot table summary and report from the pivot table results.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[byundt]

Just out of curiosity, before you developed the code to track the changes to the workbook--did you investigate the Track Changes feature built into Excel?

Track Changes has the capability of creating a History sheet that shows every single change made to the workbook--who, when, where, old value, new value, worksheet.

To access this information, open the Tools...Track Changes...Highlight Changes menu item and check "Track Changes While Editing". Now make some changes. Then open the Tools...Track Changes...Highlight Changes menu item once more, select "All changes" made by "Everyone". Finally, check the box for "List changes on a new sheet". This last step creates a worksheet called History that contains all the information listed above. You can copy that information to a new worksheet and Sort/Filter it as desired.

 

[FaneDuru]

Very nice and interesting...
In order to keep selection after change it is necessary an other variable to memorize position (NewLocation):

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim oldVals As Variant, newVals As Variant, NewLocation As Range
    Dim x As String
    Dim i As Long, j As Long
    Const LogFileName As String = "C:\MyFiles\Tracker.txt"
    Dim FileNum As Integer
 
    Application.EnableEvents = False
    newVals = Target.Value: [COLOR=red]Set NewLocation = ActiveCell[/color]
    Application.Undo
    oldVals = Target.Value
    Target.Value = newVals: [COLOR=red]NewLocation.Select[/color]
    Application.EnableEvents = True
    
    FileNum = FreeFile
    Open LogFileName For Append As #FileNum
    For i = 1 To Target.Rows.Count
        For j = 1 To Target.Columns.Count
            If Target.Cells.Count > 1 Then
                x = Sh.Name & "!" & Target.Cells(i, j).Address & " was changed by " & Application.UserName & _
                    ", from " & IIf(oldVals(i, j) = "", " nothing", oldVals(i, j)) & ", in " & newVals(i, j)
            Else
                x = Sh.Name & "!" & Target.Address & " was changed by " & Application.UserName & _
                    ", from " & IIf(oldVals = "", " nothing", oldVals) & ", in " & newVals
            End If
            Print #1, x
        Next
    Next
    Close #1
End Sub

Fane Duru

 

[xlbo]

Can be done using the SELECTION change event also...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[byundt]

What happeneded to the four or more posts that occurred on September 6 and 7 (just before FaneDuru's post of September 7)? I'm not able to see them at all.

Were they deleted by an editor? Moved to a new thread? Temporarily lost due to a server hiccup?

 

[xlbo]

Probably Red Flagged and deleted by site mgmt - could've been for tacking onto an old thread - Fane Duru and myself must've posted after the thread was chopped

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[FaneDuru]

I have only improved one nice previous thread... Now like a chalange: How can we trigger row deletion or row inserting? (We can identify row selection using Target.Columns.Count = 256)

Fane Duru

 

[byundt]

Fane Duru,
You can use the Worksheet_Calculate event sub to detect row or column insertion/deletion. The trick is to make the worksheet volatile (such as by putting a formula like =NOW() in a cell). You then have a named range that points to cell IU65535. If a row or column is inserted or deleted, that cell reference will move. Since the worksheet is volatile, the calculate sub will run and the change can be immediately detected.

Code to trap a row insertion/deletion is shown at

http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21056640.html#11538263

Brad