New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Add The Date To Each Row Automatically As You Create Entries

Free Excel Macros

Add The Date To Each Row Automatically As You Create Entries

Postby Sisyphus » Thu Mar 01, 2012 11:57 pm

Excel enables you to write a date in a cell using Ctril+;.
As if that weren't easy enough, here is a macro that write the date without pressing any keys AND move the cursor to the next cell. Install this code in a normal code module (by default 'Module1'):
Code: Select all
Public Sub CreationDate(ByRef Target As Range)
   
    Const CreateColumn As String = "B"
   
    With Target
        With .Worksheet.Cells(.Row, CreateColumn)
            If Not IsDate(.Value) Then .Value = Date
            .Offset(0, 1).Select
        End With
    End With
End Sub
You can select the column in which you want the date written. Just change the constant's 'CreateColumn' value. However, no date will be written if the target cell already has a date. So, if you use this function to record the date on which the entry was made the original date is preserved in case of later changes.

The above code is triggered by a Change Event in the worksheet where you want the date written. Paste the following code in the code sheet of the worksheet in which you wish to write the date (by default 'Sheet1'):
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)

    Const TriggerColumn As String = "A"
    Const HeaderRows As Long = 1
   
    With Target
        If .Column = Asc(TriggerColumn) - 64 Then
            If .Row > HeaderRows And _
                Trim(.Value) <> vbNullString Then _
                CreationDate Target
        End If
    End With
End Sub

It makes sense to let the date be added whenever you start writing in a row, i.e. whenever you create a new row at the bottom of your worksheet, but if you want the TriggerColumn to be a column other than column A just change the constant's TriggerColumn value to any value from B to Z. The constant HeaderRows indicates the number of rows at the top of your sheet where you don't want any date written, ever. You can set any value from zero up, but not below.
This code will call the date-writing procedure if the change is made anywhere in the TriggerColumn except in the header rows and provided that the changed cell isn't blank after the change.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 28 guests