CODEWITHSUNDEEP
vbaexcel
Tango_Mike
Tango_Mike

Reputation: 104

Disabling cell auto-update

I have a cell which I want to record the time when adjacent cells to the left are changed. I do it with the NOW() function; however, the problem is that the time gets updated each time workbook is re-calculated. So, I am wondering whether there is any original way to prevent this very cell from auto-updating.

I personally have come up with this:

Private Sub Workbook_Open()
 Application.Calculation = xlCalculationManual
End Sub

And this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.Calculation = xlCalculationManual
End Sub

So the question is whether there is any other better way to implement this issue.

I would also appreciate any ideas of how I could record the time, other than the "NOW()" function I currently use, so that it is recorded ONLY when adjacent data is changed and not when the workbook itself is re-calculated.

Thank you very much in advance!

Upvotes: 0

Views: 166

Answers (1)

teylyn
teylyn

Reputation: 35990

Instead of the =Now() function in the cell and running a macro to recalculate, try a worksheet change event that monitors column A and puts the time stamp in column B as a fixed value when A is changed.

Something along the lines of

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel as Range
    if not intersect(target, Range("A:A")) is nothing then
       target.offset(0,1) = Now
    end if
End Sub

Upvotes: 1

Related Questions