Time Stamp Updating when it shouldn't

[rvinci]

Hey all,

I have the below pieces of code


Time Stamp

Option Explicit
Private Sub Worksheet_Calculate()
Dim CELL As Range
Application.EnableEvents = False
    For Each CELL In Range("I3:I32")
    
If CELL.Value <> CELL.Offset(, 26).Value Then
            With CELL.Offset(, -1)
              .NumberFormat = "mm/dd/yy h:mm"
              .Value = Now
            End With
            CELL.Offset(, 26).Value = CELL.Value
        End If
    Next CELL
Application.EnableEvents = True
End Sub

Sort

Sub Sort()
'
' sort Macro
' Macro recorded 5/21/2010 by rvinci
    Rows("3:32").Select
    Selection.Sort Key1:=Range("G3"), Order1:=xlAscending, Key2:=Range("H3") _
        , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _
        xlSortNormal
End Sub

My issue is that when I go to sort, the time stamp is still updating when it should only be updating based on the formula in Column I changing. I have another program that uses the same code and the time stamp is fine. Can anybodyhelp me figure out why the time stamp updates?

 

[njimack]

The event fires every time the sheet is calculated, which could include every time it's sorted. Try setting EnableEvents to False at the start of your Sort Sub, and setting back to True at the end.

 

[Jonmo1]

it should only be updating based on the formula in Column I changing.

Actually that's not accurate.

The Calculate event is triggered anytime ANY formula is calcluated (among other triggers as well, like Sorting)

disable events in the first line of the Sub with

Application.EnableEvents = False

Then reset to true at the end

Application.EnableEvents = True

 

[rvinci]

That did the trick, thank you both!

 

[jsotola]

your code should go into worksheet change event handler code

it runs only if any cell in Range("I3:I32") gets changed

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim CELL As Range
    
    If Intersect(Target, Range("I3:I32")) Is Nothing Then Exit Sub    ' check if "watched" cells have changed
    
    Application.EnableEvents = False
    
    For Each CELL In Range("I3:I32")
        If CELL.Value <> CELL.Offset(, 26).Value Then
            
            With CELL.Offset(, -1)
                .NumberFormat = "mm/dd/yy h:mm"
                .Value = Now
            End With
            
            CELL.Offset(, 26).Value = CELL.Value
        
        End If
    Next CELL
    
    Application.EnableEvents = True


End Sub

 

[Jonmo1]

your code should go into worksheet change event handler code

it runs only if any cell in Range("I3:I32") gets changed

Except that the values in I3:I32 are 'formulas'
Changes to the result of a formula do not trigger the worksheet_change event.

That's why the original code is using the worksheet_calcluate event.