I have a requirement to add change auditing to an Excel 2010 workbook. If the user changes the value of a cell in columns A, B, C, or J, I need to capture the old value, the datetime of the change, the username, and the new value that they enter. I was able to do that - this code captures the old value from the selected cell (I have another sub that does the rest):
The problem is, users sometimes need to copy a value and paste it into another cell - for example, copy the value in cell A22 and paste it into cell A23. But the Worksheet_SelectionChange event clears the clipboard, so they can't paste. if I add an If..Then to check whether Application.CutCopyMode = False, then the SelectionChange event doesn't fire and the old value isn't recorded.
Is there any way to capture the existing value of the selected cell without losing the ability to copy/paste?
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Long
Dim OldValue As String
Dim AuditRecord As Range
Dim wsManifest As Worksheet
Dim wsHistory As Worksheet
Set wsManifest = ThisWorkbook.Worksheets("manifest template")
Set wsHistory = ThisWorkbook.Worksheets("ChangeHistory")
'Limit range for change auditing to columns A, B, C, and J
If (Not Application.Intersect(Target, Range("Tracking")) Is Nothing) And (Target.Row >= 3) Then
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
' This is our change history ...
Set AuditRecord = wsHistory.Range("A:E")
r = 0
' Now find the end of the Change History to start appending to ...
r = FindLastRow(wsHistory.Range("A:A"))
'Old value - Preserve formats
OldValue = wsManifest.Cells(Target.Row, Target.Column).Value
If OldValue = "" Then
With AuditRecord.Cells(r, 3)
.Value = "New Entry"
.NumberFormat = "@"
.HorizontalAlignment = xlLeft
End With
Else
wsManifest.Cells(Target.Row, Target.Column).Copy
AuditRecord.Cells(r, 3).PasteSpecial xlPasteAll
Application.CutCopyMode = False
End If
End If
End Sub
The problem is, users sometimes need to copy a value and paste it into another cell - for example, copy the value in cell A22 and paste it into cell A23. But the Worksheet_SelectionChange event clears the clipboard, so they can't paste. if I add an If..Then to check whether Application.CutCopyMode = False, then the SelectionChange event doesn't fire and the old value isn't recorded.
Is there any way to capture the existing value of the selected cell without losing the ability to copy/paste?