How can I determine new & previous cell value on SheetChange event in Excel?

Question

I have some special cells in my Excel workbooks which are managed by my Excel Add-in. I want to prevent users from changing content of those cells, but I also want to know, what value users wanted to enter to those cells. On the SheetChange event I can check what users entered to my special cells, but how do I determine the PREVIOUS value in those cells and REVERT user changes?

Answer 1

If you need to prevent the users from changing the values, why not lock the cells (right click, format-cell, locked) and then protect the worksheet(tools-protection-protect worksheet). When the cells are being programatically changed, change the locked property and change it back after computation

Answer 2

It is not a solution for me. If I lock cell in Excel, it becomes read-only - user can not even try to enter anything to this cell - Excel popups warning dialog in this case. My problem is that I want to catch what user entered to my cell, do something with this value, and then revert cell content to original value.

Answer 3

How about something like this, which is in VBA, but should be fairly easy to translate to C#

Option Explicit

' We are monitoring cell B2...

Private initialB2Value As Variant  ' holds the value for reinstatement when the user changes it

Private Sub Worksheet_Activate()
' record the value before the user makes any changes.
' Could be a constant value, or you could use .Formula to ensure a calculation is not lost
    initialB2Value = Range("B2").Value

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Static alreadyChanging As Boolean 
' when we reset the cell, Worksheet_Change will fire again, so we'll use a flag
' to tell us if we should care or not...

    If alreadyChanging Then ' change is because of this code, no need to process
        alreadyChanging = False
        Exit Sub
    End If

    If IsEmpty(Intersect(Target, Range("B2"))) Then
    ' If the change is not happening to the range we are monitoring, ignore it
        Exit Sub
    End If

    ' Do something with the user's input here
    Debug.Print "User input " & Range("B2").Value & " into B2"

    ' before we reset the value, flag that we are changing the value in code
    alreadyChanging = True

    ' now apply the old value
    Range("B2").Value = initialB2Value

End Sub

Answer 4

I would keep a copy of the cells on a hidden sheet.

Then when the user changes a cell, it's easy to find the matching cell on the hidden sheet.

Answer 5

Perhaps it would suit to capture the value on entering the cell:

Option Explicit
Dim LastText As String

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
        ByVal Target As Excel.Range)
    LastText = Target.Value
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
        ByVal Source As Range)
    Debug.Print LastText; Source
End Sub

Answer 6

I resolved this using Application.Undone() checking if the change is not valid.