- 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"|
|' Limit the area of the spreadsheet to which this code applies|
|If Intersect(Target, Me.Range("A2:A65000")) Is Nothing Then Exit 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.