Excel Coding:  Automatic Datestamp, Field and Row Advance

Posted on 2014-08-08
Last Modified: 2014-08-11
Hi.  I'd like a little help with coding an Excel sheet.

I would like that when data is entered into the first cell (A1), the timestamp of entry automatically appears in B1 and cursor advances to C1 for next input.

When data is entered to C1, I would like the sheet to advance to the next row and begin the process over again with cursor awaiting input in A2.
Question by:mrherndon
    LVL 44

    Expert Comment

    Code something like the following into the Sheetchange event at either the worksheets or workbook objects.
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        If Target.Column = 1 Then
            Target.Offset(0, 1).Value = Now
        End If
    End Sub

    Open in new window

    LVL 14

    Accepted Solution

    Actually it would be something more like this...

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Cells.Count = 1 Then
            Application.EnableEvents = False
            If Target.Column = 1 Then
                Target.Offset(0, 1).Value = VBA.Now
                Target.Offset(0, 2).Select
            ElseIf Target.Column = 3 Then
                Target.Offset(1, -2).Select
            End If
            Application.EnableEvents = True
        End If
    End Sub

    Open in new window

    This goes into the worksheet module. To install, right click the worksheet tab, select View Code, paste in the window. There can only be one Change event per module. Close the VBE window and then it should work.


    Zack Barresse

    Author Closing Comment

    Thanks very much.  EXACTLY what I was looking for.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    This article will show, step by step, how to integrate R code into a R Sweave document
    Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now