In order to gauge financial strength and create proper forecasting, it is necessary to collect data from multiple sources. Within that data collection, it is also necessary to provide datasheets to others. When sharing worksheets, Excel will allow you to utilize track changes to see what was edited; however, with regards to user functionality, this method falls short.
In this recipe, you will learn to create an add-in to an Excel worksheet that will not only track changes, but will also mark the changes to identify the location of the changed data and create a cell comment with the past values for easy reversal and historical tracking.
We will start by opening the Visual Basic Editor (VBE) in Excel and displaying a blank code window:
- Within Excel, press Alt + F11 to open the visual basic editor (VBE):
- Double click on the label for Sheet1 to open a blank code window.
Excel will display Sheet1 at the top of the window and the drop-down will display General:
- Enter the following code:
Option Explicit Public previous As Variant Private Sub Worksheet_Change (ByVal Target As Range) If Target.Count > 1 Then Exit Sub Target.Font.Color = RGB(255, 0, 0) Target.ClearComments Target.AddComment.Text Text:="Original entry " & preValue & Chr(10) & "Editing occurred " & Format(Date, "mm-dd-yyyy") & Chr(10) & "Editing User: " & Environ("UserName") End SubAfter entering this code, Excel should automatically create separation lines in the code to break up the declaration versus the actual code:
- Enter the second portion of the code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If Target = "" Then previous = "a blank" Else: previous = Target.Value End If End Sub
- Save the worksheet and close the VBE.
- In the original worksheet, make a change to the data contained in one of the cells:
As you can see, the font color has changed to red and a comment has been added to the cell with the previous information, the date, and the username of the person that made the change.
Beginning with the first set of code, we set up the variable to store the cell value of what will be edited by the user:
Option Explicit
Public previous As VariantThis is explicitly declared and made public so that any further code will already have this variable available, and the information in the variable may be passed between blocks of code:
Private Sub Worksheet_Change (ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Target.Font.Color = RGB(255, 0, 0)
Target.ClearComments
Target.AddComment.Text Text:="Original entry " & preValue & Chr(10) & "Editing occurred " & Format(Date, "mm-dd-yyyy") & Chr(10) & "Editing User: " & Environ("UserName")
End Sub
Next, we wait for information on the sheet to change, and we trigger Excel to check if that change has occurred within a cell. If so, Excel working in tandem with the next block of code, records the original cell value and passes this information to the worksheet change event. Here, the event changes the font color of the cell, and creates a cell comment to record the change details:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If Target = "" Then previous = "a blank" Else: previous = Target.Value End If End Sub
This last block is what captures the original value and passes it to the worksheet change event.
Variations of this user edit flagging can be created by adding an apostrophe at the beginning of specific lines to suppress the full code. An example of this suppression would be to stop the cell comment from being added and only allowing the font color change. This can be accomplished by changing the first block as follows:
Private Sub Worksheet_Change (ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Target.Font.Color = RGB(255, 0, 0)
'Target.ClearComments
'Target.AddComment.Text Text:="Original entry " & preValue & Chr(10) & "Editing occurred " & Format(Date, "mm-dd-yyy") & Chr(10) & "Editing User: " & Environ("UserName")
End Sub