Time Stamp Updating when it shouldn't

rvinci

New Member
Joined
Jan 28, 2013
Messages
48
Hey all,

I have the below pieces of code


Time Stamp

Code:
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

Code:
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?
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
 

jsotola

Well-known Member
Joined
Nov 15, 2013
Messages
524
your code should go into worksheet change event handler code

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

Code:
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top