Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Track Changes in a workbook (Excel 2003)

    hello all,

    I want to track changes made in a worksheet in a log table but i could not find a working solution...
    The required fields are:

    Field Data Type Explanation
    EditRecordID AutoNumber Audit's Primary Key.
    EditDate Date/Time The date and time change is made.
    User Text The user who made the change.
    SourceTable Text Table or query.
    SourceField Text The field changed.
    BeforeValue Text The original value before change.
    AfterValue Text The new value after change.

    if the changes are > 65534, the cells can be overwritten. Does anyone know,
    how to do this?

    Stefan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Track Changes in a workbook (Excel 2003)

    If you really need this, I'd use a server database such as Microsoft SQL Server, or Oracle. They have logging all changes built in.

    Adding the kind of change tracking that you want to an Excel workbook would make it very slow, and it would disable the undo feature, so it would make the workbook hard to use.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Track Changes in a workbook (Excel 2003)

    hi Hans,

    the attached workbook makes almost the most things i wanted...

    best regards
    stefan
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Track Changes in a workbook (Excel 2003)

    Your code will fail if the user modifies several cells at once (for example by selecting a range and pressing Delete). The following version handles modifying multiple cells:

    Sub Worksheet_Change(ByVal Target As Range)
    Dim myRng As Range
    Dim myCell As Range
    Dim r As Long

    Set myRng = Range(Sheets("Sheet1").[A1], Sheets("Sheet1").[C50])

    If Not Intersect(myRng, Target) Is Nothing Then
    For Each myCell In Intersect(myRng, Target)
    r = Sheets("Record").Cells(65536, 1).End(xlUp).Row + 1
    Sheets("Record").Cells(r, 1) = Date & " " & Time
    Sheets("Record").Cells(r, 2) = myCell.Address
    If myCell.Value = "" Then
    Sheets("Record").Cells(r, 3) = "Value deleted"
    Else
    Sheets("Record").Cells(r, 3) = myCell.Value
    Sheets("Record").Cells(r, 5) = Environ("USERNAME")
    End If
    Sheets("Record").Cells(r, 4) = ThisWorkbook.BuiltinDocumentProperties(7)
    Next myCell
    End If
    End Sub

  5. #5
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Track Changes in a workbook (Excel 2003)

    Thank You, Hans!!!!!!

    stefan

  6. #6
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Track Changes in a workbook (Excel 2003)

    as a last add, i wanted to get the original data via vba-vlookup instead of a formula to the recordsheet,
    but i guess, my code does not wor (see module in attached file). Does someone know, how to solve this?

    stefan
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Track Changes in a workbook (Excel 2003)

    You set the range rngData incorrectly (you're making it too complicated). Try this line:
    <code>
    Set rngData = .Range(.Cells(2, 3), .Cells(2, 4).End(xlDown))</code>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •