How to create a timestamp only when a specific value is entered

Question

I'm trying to write a sub that creates a timestamp in column J when the text "YES" is entered in column I.

Right now I've got a working sub that creates a timestamp in column J when a cell in column I is changed. But my attempts to change this sub to fit the above criteria have failed so far. Could you please advise or point me in the right direction?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim speakRange As Range
Dim timeSpeakRange As Range

Set speakRange = Range("I3:I1000")

If Intersect(Target, speakRange) Is Nothing Then Exit Sub

'Stop
Application.EnableEvents = False

'column for timestamp
Set timeSpeakRange = Range("J" & Target.Row)


'Determine if the input date/time should change

If timeSpeakRange .Value = "" Then

    timeSpeakRange .Value = Date

End If


'Turn events back on
Application.EnableEvents = True


End Sub

TIP: since you are working with Worksheet Change event, you may also want to see [THIS](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) — Siddharth Rout, Feb 25 '20 at 16:09

urdearboy · Answer 1 · 2020-02-25T15:58:56.087

0

Instead of having a early exit, negate your statement. From there, run the simple check of Target = "YES" and if so, access the cell to the right using Offset(0, 1) to add date.

You may need to format date & also checkout @BigBen comments below for ideal error handling

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("I:I")) Is Nothing Then
    If Target = "YES" Then
        Application.EnableEvents = False

            Target.Offset(, 1) = Date

        Application.EnableEvents = True
    End If
End If

End Sub

edited Feb 25 '20 at 15:58

answered Feb 25 '20 at 15:49

urdearboy

14,439
5
28
58

But this will fail if `Target` is a multi-cell range. – BigBen Feb 25 '20 at 15:49
... but should you assume that? Better safe than sorry. Code with assumptions is code waiting to blow up, IMO. – BigBen Feb 25 '20 at 15:50
1

It's pretty easy to change to `If Target.Cells(1).Value` :-) Problem solved! Or this can be a teaching moment for OP. – BigBen Feb 25 '20 at 15:53
@BigBen - when can multiple cells be changed? Is it just when pasting? – SJR Feb 25 '20 at 15:58
1

@SJR - also if you select a bunch of cells, enter something, and confirm with Ctrl+Shift+Enter. – BigBen Feb 25 '20 at 15:59
1

I get your point @BigBen - I was focused on correcting other things in OPs attempt while taking post literal. Anticipating other errors is def the way to go, but there was already other issues to start with. Wondering how **spill over ranges** impact this as well. Prob same as prior array formulas – urdearboy Feb 25 '20 at 16:01
1

Well to be really safe - `Target.Cells(1)` might not necessarily correspond to a cell in Column I - for example if `Target` is `H10:J10`... you really want to work on the *intersection* of `Target` and column I to be safe. – BigBen Feb 25 '20 at 16:03

How to create a timestamp only when a specific value is entered

1 Answers1