VBA Express : Excel - Tracking Changes With Comments

Excel

Tracking Changes With Comments

Ease of Use

Easy

Version tested with

2003

Submitted by:

lenze

Description:

The code given here will autoimatically add a comment to a cell whenever it is changed. The comment gives the cell's previous value, the date it was changed and the user who made the change.

Discussion:

Many times several people use the same workbook and make changes to it. The Track Changes feature in Excel can allow you to see these changes, but is awkward to work with. By simply adding a comment to the cell when it is changed, you have an easy readable record of what has occurred. The code uses 2 Event Procedures, the WorkSheet_Change Event and the WorkSheet_SelectionChange Event. SelectionChange sets a public variable to be used by the Change Event to record the value of the cell before the change. The Change Event adds the comment.

Code:

instructions for use

			Option Explicit 
Public preValue As Variant 
Private Sub Worksheet_Change(ByVal Target As Range) 
    If Target.Count > 1 Then Exit Sub 
    Target.ClearComments 
    Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName") 
End Sub 
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    If Target.Count > 1 Then Exit Sub 
    If Target = "" Then 
        preValue = "a blank" 
    Else: preValue = Target.Value 
    End If 
End Sub

How to use:

Open a WorkBook and select the sheet you want to track changes on.
Right_Click on the sheet tab and choose view code to open the VBE
Copy the code into the panel on the right. Exit the VBE using ALT+Q

Test the code:

Once the code is in place, simply make changes to any cell. You will see the comment appear automatically.
The example workbook has different sheets where I have isolated various ranges for the code to run on. You can see how this was done by viewing the code on each sheet.
If you wanted the same ranges to be commented on all sheets, you could place the code in the ThisWorkbook module's Change Event.

Sample File:

Comment_Example.zip 10.91KB

Approved by mdmackillop

This entry has been viewed 412 times.