[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

Excel Coding: Automatic Datestamp, Field and Row Advance

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.
1 Solution
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

Zack BarresseCEOCommented:
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
mrherndonAuthor Commented:
Thanks very much.  EXACTLY what I was looking for.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now