VBA question - enter timestamp in more than one cell

Caitlin535

New Member
Joined
Jan 8, 2016
Messages
18
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,963
Office Version
  1. 365
Platform
  1. Windows
Just put another line of code under this one:
Code:
Target.Offset(0, 1) = Format(Now(), "dd-mm-yyyy hh:mm:ss")[/COLOR]
that looks like this:
Code:
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

New Member
Joined
Jan 8, 2016
Messages
18
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,963
Office Version
  1. 365
Platform
  1. Windows
I have made a few minor additions to your code:
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:
Code:
Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub
 

Caitlin535

New Member
Joined
Jan 8, 2016
Messages
18
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?
 

Forum statistics

Threads
1,141,204
Messages
5,704,951
Members
421,372
Latest member
Jamie11

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