Wednesday, March 09, 2005

Quick Excel VBA solution for a friend today

Today I helped a friend devise a quick and easy method of marking entries in a long excel list with a date stamp in the column directly left of the entry. However, if the entry was an edit of a prior entry, the date stamp that was placed there before had to stay put.

Here's the quick of it...

Private Sub Worksheet_Change(ByVal Target As range)

' This macro runs everytime a change is made to any one or more cells in a worksheet.
' The changed cells are defined at "Target".
' Right now this is not written to handle it if you were to change (delete, for example) multiple cells at once.

'We'll specify that we only care about changes made in column "B" or "2" as it's known in VBA.
If Target.Column = 2 Then

' If the change is made to a cell (or more) in the column specified, then we check to see if
' it had been stamped before. If not, we stamp it, otherwise we don't.
If Target.Offset(0, -1).Value = "" Then
Target.Offset(0, -1).Select
Selection.FormulaR1C1 = "=today()"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Offset(1, 1).Select
End If
End If

End Sub

No comments: