Enter date by double-clicking an Excel cell

Ever wanted to quickly enter the date in an Excel spreadsheet cell without typing it or looking up what date it is?
Here's how

• In the worksheet you want this to occur in, press Alt-F11 to open the MS VB Editor dialog
• Make sure the Project Explorer widow is open by selecting Ctrl + R.
• In the Project Explorer, double-click the worksheet you want to add dates to, and this will open the code page for that worksheet (e.g. Sheet1)

• Copy and paste the following code in the resulting blank code page

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim CellTime, CellState

' Limit the effect of this code to single-cell selections

If Target.Cells.Count > 1 Then

MsgBox "More than one cell selected", vbInformation + vbOKOnly, "Selection Error"

Exit Sub

End If

' Limit the area of the spreadsheet to which this code applies

If Intersect(Target, Me.Range("A2:A65000")) Is Nothing Then Exit Sub

' Insert approved time in cell If ActiveCell <> "" Then iCellState = MsgBox("Cell already has data. Overtwrite ?", vbExclamation + vbYesNo, "Data Overwrite Warning") End If If iCellState = vbNo Then SendKeys "{Esc}" Else ActiveCell = Date SendKeys "{Enter}" ActiveWorkbook.Save End If End Sub

A couple of things to note:
The section commented as Limit the area of the spreadsheet to which this code applies limits the double-clicking entry of dates to the range specified.
In the example it is any cell from A2 to the bottom of the spreadsheet in the A column.
Change this range address to the area you want to insert dates on your spreadsheet

If the cell already has data in it, you will be prompted to confirm the replacement of that data with the current date

If more than one cell is selected when you double-click you will also get a warning

If you need help extending or modifying this code to suit your needs, give me a shout.