Nate Oliver's blog

Good day,

This is based on a question that was posed the other day, how to date-stamp a completed record, in Excel.

Before we start, a primer on Excel Event Procedures, which is highly recommended reading, can be found here:

http://www.cpearson.com/excel/Events.aspx

With all of this in mind, let's explore the Worksheet Change Event.

Basically, it's code that detects when and where you changed a cell in the Worksheet, while hooking the changed cells, giving you the Range. So, my example comes from a question, how do I add dates to a completed Record? Which I assume comprises multiple fields (or cells). Here is my example:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl As Range, lngCalc As Long
Const constRows As Long = 1000
Const constCols As Long = 5
If Intersect(Target, Cells(1, 1).Resize(constRows, constCols)) _
    Is Nothing Then Exit Sub
With Application
    '---- .EnableEvents = False
    Let lngCalc = .Calculation
    Let .Calculation = xlCalculationManual
    Let .ScreenUpdating = False
    For Each cl In Target
        If LenB(Me.Cells(cl.Row, constCols + 1).Value) = 0 Then
            If .WorksheetFunction.CountA( _
                Me.Cells(cl.Row, 1).Resize(1, constCols)) = constCols Then _
                    Let Me.Cells(cl.Row, constCols + 1).Value = Now()
        End If
    Next cl
    '---- .EnableEvents = True
    Let .Calculation = lngCalc
    Let .ScreenUpdating = True
End With
End Sub

To insert such a functional procedure, the simplest way is to right-click on the Worksheet tab in question, and left-click 'View Code'. Bingo.

To the code. Target is a Range, it's not necessarily a single-cell Range, hence I loop - this is a little sloppy, but effective. I'm not a big fan of bailing on Event Procedures when a multi-cell Target exists, as it's common to copy and paste into Excel.

The way I've constructed this is that the max test is 1,000 rows, and 5 columns; therefore, dropping the date in column F, when complete. I also built it to short-circuit if there's already a date in column F. If you need more rows or columns, change the constants.

I've added, and commented-out, the EnableEvents Parameter of the Application. They're not necessary given this construct, but they are generally important to keep in mind. This was written and tested in Excel 2010, but can be used across any platform.

Cheers, Nate