Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

Excel: VBA tracking. (VBA updates any changes made to worksheet.)

Normally I use Tools-->Track Changes --->Highlight Changes.

As you know this will show a note on each cell that is changed with the following information...

1) The users name
2) Time user made the change
3) A description of the change.

I would like to create VBA that does something similar. (I don't want to use "Track Changes"...)

Therefore....What VBA would I need so that every time a user makes a change to "Sheet1" of my Excel file, on Sheet 2 column A it will log in a cell the users name/computer name, current date and if possible the first 20 characters of the change.

i.e. if the user named "Bobby" goes to "Sheet1" and updates cell C1 to "Hello on 6/7/10" then updates cell E20 to "goodbye" on 8/1/10
the following information will show on "Sheet2" in columnA

         column A
Row1: 6/7/10, Bobby, "Hello"
Row2: 8/1/10, Bobby, "goodbye"



0
ouestque
Asked:
ouestque
  • 2
1 Solution
 
Dave BrettCommented:
  • right click your sheet1 sheet tab
  • View - Code
  • copy and paste the code below
This code will handle multiple cell updates at once, plus it returns the logged on username - not the username entered as an Option in Excel which users can change

Note that you need to change the Sheet2 name if the second sheet is renamed

Cheers

Dave

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim c As Range
    Set ws = ThisWorkbook.Sheets("Sheet2")
    For Each c In Target
        If Not ws.[a1] = vbNullString Then
            ws.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = Format(Now(), "d/mm/yy, ") & Environ("username") & ", " & c.Value
        Else
            ws.[a1] = Format(Now(), "d/mm/yy, ") & Environ("username") & ", " & c.Value
        End If
    Next c
End Sub

Open in new window

0
 
Dave BrettCommented:
If you wanted to capture the cell address being changed then use

Format(Now(), "d/mm/yy, ") & Environ("username") & ", " & c.Address(0, 0) & ", " & c.Value
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now