Results 1 to 5 of 5

Thread: Code to insert current date and time when a value is enter into column B

  1. #1
    New Member
    Join Date
    Aug 2015
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Code to insert current date and time when a value is enter into column B

    Hello,

    Thanks for taking you time to read my problem. I am tracking my blood pressure. With Column A being current date and time (m/d/yy
    h:m AM/PM).

    What I envision is once I but the first number into column B. That a code would insert the current date and time. Then make my active cell as column C to enter the next number.

    I found this code on the internet. But, I can not figure out the offset correctly. Plus, I know I have to change the Target Column to 2. But, it is kicking my backside.

    'Code by Sumit Bansal from https://trumpexcel.com
    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'Code by Sumit Bansal from https://trumpexcel.com

    Any help would be appreciated.

    Thanks

    Kevin

  2. #2
    Board Regular
    Join Date
    Oct 2014
    Posts
    88
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code to insert current date and time when a value is enter into column B

    Code for insert to Column Next
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Static i As Integer
    On Error GoTo Handler
    If Target.Column = 1 And Target.Value <> "" Then
    Application.EnableEvents = False
    
    For i = 1 to 200
    If Target.Offset(0, i) <> "" Then Exit For
    Next
    
    Target.Offset(0, i) = Format(Now(), "dd-mm-yyyy hh:mm:ss")
    
    Application.EnableEvents = True
    End If
    Handler:
    End Sub

  3. #3
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,603
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Code to insert current date and time when a value is enter into column B

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count <> 1 Then Exit Sub
    Application.EnableEvents = False
    If Target.Column = 2 Then
        If Target.Value = "" Then
            Target(1, 0).ClearContents
        Else
            Target(1, 0) = Format(Now(), "dd-mm-yyyy hh:mm:ss")
            Target(1, 2).Select
        End If
    ElseIf Target.Column = 3 Then Target(2, 0).Select
    End If
    Application.EnableEvents = True
    End Sub
    Last edited by footoo; Apr 24th, 2019 at 10:35 PM.

  4. #4
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,590
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Code to insert current date and time when a value is enter into column B

    This version will undo copy/paste to multiple col B cells at a time and clear date/time from col A if user wants to delete a prior col B entry.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Columns("B")) Is Nothing Then
        If Target.Count = 1 Then
            If Target.Value <> "" Then
                Application.EnableEvents = False
                Target.Offset(0, -1).Value = Now
                Target.Offset(0, 1).Select
                Application.EnableEvents = True
                Exit Sub
            Else
                If Not IsEmpty(Target.Offset(0, -1)) Then
                    Application.EnableEvents = False
                    Target.Offset(0, -1) = ""
                    Target.Offset(0, 1).Select
                    Application.EnableEvents = True
                    Exit Sub
                End If
            End If
        End If
        MsgBox "Confine column B entries to one cell per entry"
        With Application
            .EnableEvents = False
            .Undo
            .EnableEvents = True
            Exit Sub
        End With
    End If
    End Sub
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  5. #5
    New Member
    Join Date
    Aug 2015
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code to insert current date and time when a value is enter into column B

    Thank you all!

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
  •