通过公式（Excel）更改单元格时的时间戳 [英] Timestamp when a cell is changed via a formula (Excel)

问题描述

我需要一种方法来通过公式改变其值改变的相邻单元格的时间戳。以这个为例I需要与单元格1上的A1相邻的单元格来计时单元格值更改的日期和时间。

上面链接的示例在单元格中启动了一个消息框通过公式进行值更改（当工作表_change事件包含由于其他地方的单元格更改而改变其值的公式时，工作表_change事件似乎不会识别单元格值的更改）。我不想要消息框，但我想要一个时间戳。

为了简单起见，我将在此链接的问题上发布说明，对此具体问题提供任何其他帮助

在Sheet1单元格A1中，将此公式

  = Sheet2！A1 + 1 
  

现在在一个模块中粘贴此代码

  Public PrevVal As Variant 
  

粘贴这在表格代码区域

  Private Sub Worksheet_Calculate（）
如果Range（A1）。 > PrevVal然后
 MsgBox更改值
 PrevVal =范围（A1）。值
如果
 End Sub 
  pre> 
 
 
最后在ThisWorkbook代码区粘贴此代码
  Private Sub Workbook_Open（）
 PrevVal = Sheet1.Range（A1）。值
 End Sub 
  
 
 
解决方案
更改 MsgBoxValue Changed to：
  Range（B1）。Value = Format（现在，dd / mm / yyyy hh：mm：ss）
  pre> 
 
 
或任何时间戳格式您需要
 
I need a way to timestamp an adjacent cell whose value changes via formula. Using this as an example I need the cell adjacent to A1 on sheet 1 to timestamp the date and time when the cell value changed.

The example I have linked to above initiates a message box when the cell value changes via formula (worksheet_change events don't appear to recognise changes to a cell value when it contains a formula whose value is changed because of a cell change elsewhere). I don't want the message box but I do want a timestamp.

For simplicity I will post the instructions at that linked question here, any additional help with this specific question is appreciated.

In Sheet1 Cell A1, put this formula 
=Sheet2!A1+1
Now In a module paste this code
Public PrevVal As Variant
Paste this in the Sheet Code area
Private Sub Worksheet_Calculate()
    If Range("A1").Value <> PrevVal Then
        MsgBox "Value Changed"
        PrevVal = Range("A1").Value
    End If
End Sub
And lastly in the ThisWorkbook Code area paste this code
Private Sub Workbook_Open()
    PrevVal = Sheet1.Range("A1").Value
End Sub

解决方案
Change MsgBox "Value Changed" to:
Range("B1").Value = Format(Now, "dd/mm/yyyy hh:mm:ss")
or whichever timestamp format you require

                        
这篇关于通过公式（Excel）更改单元格时的时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！