Timestamp Sheet When Open Or Activated

[CokeOrCrack]

The following code timestamps a sheet only when it has been edited:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("G10:K10")) Is Nothing Then
        Range("G10:J10") = Now
        Range("K10") = Sheets("Numbers").Range("BC2")
        Cancel = True
    End If
End Sub

The problem is that it will enter a new timestamp when the workbook has been opened/activated.

Question

Is there a way to make it so that only USER edits prompt a timestamp?

I would like to prevent the sheet activation from prompting a timestamp.

Can this be done in an if statement?

Thanks

OJ

 

[Dim Me as xlNoob]

You can try putting this in the workbook module:

Public ignoreStamp As Boolean
Private Sub Workbook_Open()
    ignoreStamp = True
End Sub

Then adding an IF statement to the start of your sheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If ignoreStamp = True Then ignoreStamp = False: Exit Sub
    If Intersect(Target, Range("G10:K10")) Is Nothing Then
        Range("G10:J10") = Now
        Range("K10") = Sheets("Numbers").Range("BC2")
        Cancel = True
    End If
End Sub

It should ignore the sheet's code the first time when it's opened then run it after that.

 

[CokeOrCrack]

Thank you for the help. This does not appear to be working. I have the code placed in the correct module and locations, but it still provides a new timestamp upon opening the workbook.

Do I need to change ignoreStamp to False in another spot so that it moves the second If statement?

 

[Dim Me as xlNoob]

Actually I've confused myself now. I went back and tried the original code while opening the workbook the code doesn't run, it only runs when I edit cells myself. Do you have anything else running automatically whent he workbook is opened?

Also I've now realised that the value for ignoreStamp would not be stored anyway between instances of the workbook change event.

 

[CokeOrCrack]

Yes, I have a number of formulas running counts based on data within a table. These run each time the workbook is opened.

 

[Dim Me as xlNoob]

I'm not sure if this comes down to macro settings or something, but I've even included some formulas which get recalculated each time the book is opened and they still don't seem to trigger the change event on open. I need someone with more knowledge on why change events trigger and what may be causing it. I'll do some research in the meantime though.

 

[CokeOrCrack]

Has me stumped. Nothing I do changes the fact that it changes the timestamp upon open.

 

[CokeOrCrack]

Went for a run and had the idea of stripping the sheet of data and determining what was causing the issue. What I have found is that when I remove the pivot table that was on the sheet, the timestamp only changes when I (or any user in the overall case) makes an edit.

What I find even more strange is the fact that one cell on the sheet contains the formula:
=NOW()-TODAY()

Even with that formula still in the sheet (it updates and changes value all the time), the timestamp still does not change unless I specifically make an edit.

So the lesson here is that the pivot table was the cause of the issue. I have cut and pasted the pivot table to another sheet and everything works as intended.

Thanks for the help today.

 

[Dim Me as xlNoob]

Yeah I tested with RAND() and NOW() functions too to check that and I wasn't getting the issue. Good to know about how pivot tables trigger the event and formulas updating do not!