You'd have to use an event macro for this. The
Worksheet_SelectionChange() event will fire on a single click, but it
will also fire if you tab or arrow into the cell.
I'd suggest using the Worksheet_BeforeDoubleClick() event (right-click
your worksheet tab and choose View Code):
Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)
With Target
If Not Intersect(.Cells, Range("B:B")) Is Nothing Then
If IsEmpty(.Value) Then
Application.EnableEvents = False
.Value = Date
.NumberFormat = "dd mmm yyyy"
Application.EnableEvents = True
Cancel = True
End If
End If
End With
End Sub
The first time a cell in the specified range (e.g., column B) is double
clicked on, the date is inserted. Subsequent double clicks act as normal
(i.e., enter Edit mode).
