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:

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

Test the code:

  1. Once the code is in place, simply make changes to any cell. You will see the comment appear automatically.
  2. 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.
  3. 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 411 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express