VBA question - enter timestamp in more than one cell

[Caitlin535]

I have the following code that puts an automatic timestamp in Column B when a value is entered into the same row in Column A. HOWEVER, what I need is this: When a value is entered in a cell in Column A, the automatic timestamp is created in the corresponding row of Column B AND Column E. Is that possible?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Handler
If Target.Column = 1 And Target.Value <> "" Then
Application.EnableEvents = False
Target.Offset(0, 1) = Format(Now(), "dd-mm-yyyy hh:mm:ss")
Application.EnableEvents = True
End If
Handler:
End Sub

thank you!!!

Caitlin

 

[Joe4]

Just put another line of code under this one:

Target.Offset(0, 1) = Format(Now(), "dd-mm-yyyy hh:mm:ss")[/COLOR]

that looks like this:

Target.Offset(0, 4) = Format(Now(), "dd-mm-yyyy hh:mm:ss")[/COLOR]

Target.Offset(0,1) moves 1 column to the right of the target cell (column A).
Target.Offset(0,4) moves 4 columns to the right .

 

[Caitlin535]

Thank you - that solved the two cells issue. HOWEVER - it seems that I jumped the gun with that question, because the code doesn't seem to work at all! I'm pasting my exact code below - the idea is that when someone selects "Arrived" in column E it puts a timestamp in column F and column N. Similarly, when someone enters "Departed" in column G, it automatically puts a timestamp in column H and column M. Any idea what I'm doing wrong?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Handler
If Target.Column = 5 And Target.Value = "Arrived" Then
Application.EnableEvents = False
Target.Offset(0, 1) = Format(Now(), "hh:mm")
Target.Offset(0, 9) = Format(Now(), "mm-dd-yyyy hh:mm")
Application.EnableEvents = True
End If


If Target.Column = 7 And Target.Value = "Departed" Then
Application.EnableEvents = False
Target.Offset(0, 1) = Format(Now(), "hh:mm")
Target.Offset(0, 8) = Format(Now(), "mm-dd-yyyy hh:mm")
Application.EnableEvents = True
End If




Handler:
End Sub

 

[Joe4]

I have made a few minor additions to your code:

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo Handler
    
[COLOR=#0000ff]    If Target.CountLarge > 1 Then Exit Sub[/COLOR]

    If Target.Column = 5 And Target.Value = "Arrived" Then
        Application.EnableEvents = False
        Target.Offset(0, 1) = Format(Now(), "hh:mm")
        Target.Offset(0, 9) = Format(Now(), "mm-dd-yyyy hh:mm")
        Application.EnableEvents = True
    End If

    If Target.Column = 7 And Target.Value = "Departed" Then
        Application.EnableEvents = False
        Target.Offset(0, 1) = Format(Now(), "hh:mm")
        Target.Offset(0, 8) = Format(Now(), "mm-dd-yyyy hh:mm")
        Application.EnableEvents = True
    End If

Handler:
[COLOR=#0000ff]    Application.EnableEvents = True[/COLOR]

End Sub

The first new line (in blue) says that if more than one cell is updated, exit without doing anything.
So this handles the case of a line being deleted or a mass copy/paste blowing up your code.

The second line in blue re-enables events on the event you hit an error. If your code errors out after disabling events, but before it re-enables it, your code won't work anymore in that session of Excel (would need to close and re-open to run some other code to get it to work again). I am guessing that may be what happened to you.

You can manually run this short procedure to re-enable it:

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

 

[Caitlin535]

Thank you so much for the help! Per Murphy's Law, when I opened my workbook back up, the code seemed to be working. I tried with your additions anyway, and now it places the timestamp but only in the next-door column (not the one with offset 8 or 9). Is it possible that the first line - about no more than one cell being updated - is preventing it from writing the timestamp in both cells?

 

[Caitlin535]

NEVERMIND - had the worksheet locked! Sorry!