Entering todays date automatically when clicking on a cell

Question

In a ledger I am creating with Excel, I want to click a cell in the "date" row, and have each days date automatically be in the cell

hansvogelaar · Answer

going156gmailcom&nbsp;
Excel does not distinguish between clicking on a cell and moving to the cell using the keyboard, so entering the date when you click on a cell would imply that it won't be possible to visit the cell without entering the date. I'd use double-click instead of click.
Right-click the sheet tab and select 'View Code' from the context menu.
Copy the following code into the worksheet module, adjusting it as indicated.
&nbsp;
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ' Change B2:Z2 to the range in which you want the date to be entered
    If Not Intersect(Range("B2:Z2"), Target) Is Nothing Then
        Application.EnableEvents = False
        Target.Value = Date
        Cancel = True
        Application.EnableEvents = True
    End If
End Sub
&nbsp;
Switch back to Excel.
Save the workbook as a macro-enabled workbook (.xlsm).
Make sure that you allow macros when you open the workbook.

Forum Discussion

Entering todays date automatically when clicking on a cell

1 Reply

Resources