Jan 01 2021 12:38 PM
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
Jan 01 2021 01:06 PM - edited Jan 01 2021 01:51 PM
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.
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
Switch back to Excel.
Save the workbook as a macro-enabled workbook (.xlsm).
Make sure that you allow macros when you open the workbook.