C# 如何在 Excel 中的 SheetChange 事件上确定新的和以前的单元格值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/295165/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I determine new & previous cell value on SheetChange event in Excel?
提问by Falco Foxburr
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?
我的 Excel 工作簿中有一些由 Excel 插件管理的特殊单元格。我想阻止用户更改这些单元格的内容，但我也想知道，用户想要输入这些单元格的值是什么。在 SheetChange 事件中，我可以检查用户输入到我的特殊单元格中的内容，但如何确定这些单元格中的 PREVIOUS 值并恢复用户更改？
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.
这对我来说不是解决方案。如果我在 Excel 中锁定单元格，它将变为只读 - 用户甚至无法尝试向该单元格输入任何内容 - 在这种情况下，Excel 会弹出警告对话框。我的问题是我想捕获用户输入到我的单元格中的内容，使用此值执行某些操作，然后将单元格内容恢复为原始值。
采纳答案by Mike Woodhouse
How about something like this, which is in VBA, but should be fairly easy to translate to C#
像这样的东西怎么样，它在 VBA 中，但应该很容易转换为 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
回答by Dheer
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
如果您需要防止用户更改值，为什么不锁定单元格（右键单击，格式化单元格，锁定）然后保护工作表（工具-保护-保护工作表）。以编程方式更改单元格时，更改锁定属性并在计算后将其更改回来
回答by dbb
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.
然后当用户更改一个单元格时，很容易在隐藏的工作表上找到匹配的单元格。
回答by Fionnuala
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
回答by Ernesto
I resolved this using Application.Undochecking if the change is not valid.
我通过Application.Undo检查更改是否无效来解决此问题。
