Store Changes in hidden sheet

    Hello to all,


    I once had an Excel example in witch all changes made thru a user been stored on a hidden sheet.
    So later the administrator could see when and witch changes where made to the workbook.
    Now i need it i can't find this example anymore.


    I'm awere of the option of the keep track of changes function in Excel but thats not what i'm looking for.


    i'm really looking for a way to store all the changes made on a hidden sheet (saved with the workbook if closed).


    I think it has something to do with the ListChangesOnNewSheet function ore something like that.


    A search on this forum ore a Google search dident gave me the expected result.


    Any help would be great


    Thanks in advance,


    italkid.

    This example will copy the usedrange of a worksheet to a hidden worksheet each time the workbook is closed. It also puts the date and user name above the copied data. To implement this, right-mouse click on the Excel icon next to the File menu and choose View Code. Then change the drop-down that says (General) to Workbook. Then in the drop-down next to it, select BeforeClose and then paste this in:


    Private Sub Workbook_BeforeClose(Cancel As Boolean)


    Sheets("Hidden Sheet").Visible = True
    Sheets("Hidden Sheet").Activate
    If Range("A1").Value = "" Then
    Range("A1").Select
    Else
    Range("A1").End(xlDown).Offset(1, 0).Select
    End If
    ActiveCell.Value = Date
    ActiveCell.Offset(0, 1).Value = Environ("Username")
    Sheets("Data Sheet").UsedRange.Copy
    ActiveCell.Offset(1, 0).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Sheets("Hidden Sheet").Visible = False
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login