Results 1 to 6 of 6

Thread: VBA question - enter timestamp in more than one cell
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2016
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA question - enter timestamp in more than one cell

    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

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,772
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: VBA question - enter timestamp in more than one cell

    Just put another line of code under this one:
    Code:
    Target.Offset(0, 1) = Format(Now(), "dd-mm-yyyy hh:mm:ss")
    that looks like this:
    Code:
    Target.Offset(0, 4) = Format(Now(), "dd-mm-yyyy hh:mm:ss")
    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 .
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Jan 2016
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA question - enter timestamp in more than one cell

    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

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,772
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: VBA question - enter timestamp in more than one cell

    I have made a few minor additions to your code:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        On Error GoTo Handler
        
        If Target.CountLarge > 1 Then Exit Sub
    
        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:
        Application.EnableEvents = True
    
    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
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Jan 2016
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA question - enter timestamp in more than one cell

    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?

  6. #6
    New Member
    Join Date
    Jan 2016
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA question - enter timestamp in more than one cell

    NEVERMIND - had the worksheet locked! Sorry!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •