Excel

Save Cells Previous Value in a Comment

Ease of Use

Intermediate

Version tested with

2000 

Submitted by:

johnske

Description:

After changing a cell value the previous value is shown as a comment 

Discussion:

In a shared environment, there may be several users making alterations to the worksheet and you want to know what was in the cell before another user changed it. NOTE: The value saved on (in this example) Sheet2 will change whenever the cells are simply selected on the first sheet, however the previous value shown in the comment remains totally unaffected by all this, changing only when the value is actually changed. Sheet2 can thus be used as a temporary repository for values for any number of sheets using this principle. 

Code:

instructions for use 

			
Option Explicit 
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
     'copy previous value to another sheet
    Sheet2.Range(Target.Address) = Target 
End Sub 
 
Private Sub Worksheet_Change(ByVal Target As Range) 
     '//clearing more than one cell causes an error
    On Error Resume Next 
     '//(can't overwrite an existing comment)
    Target.ClearComments 
    With Target 
         'get the previous value when value changes
        .AddComment 
        .Comment.Visible = False 
        .Comment.Text Text:="Previous value = " & Sheet2.Range(Target.Address) 
    End With 
End Sub

How to use:

  1. Open an Excel workbook
  2. Select Tools/Macro/Visual Basic Editor
  3. In the VBE window, select Tools/Project Explorer
  4. Select the Sheet module you want this applied to
  5. NOTE: If Sheet2 is required for something else, change the 2 in Sheet2 to another sheet number
  6. Copy and paste the code into the sheet Module
  7. Now select File/Close and Return To Microsoft Excel
  8. Dont forget to save your changes...
 

Test the code:

  1. Start entering data in the required sheet...
  2. Hover the mouse pointer over a cell to check its' previous value
 

Sample File:

PrevValu shown as comment.zip 8.61KB 

Approved by mdmackillop


This entry has been viewed 220 times.

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