Entering todays date automatically when clicking on a cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2026127%22%20slang%3D%22en-US%22%3EEntering%20todays%20date%20automatically%20when%20clicking%20on%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2026127%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20a%20ledger%20I%20am%20creating%20with%20Excel%2C%20I%20want%20to%20click%20a%20cell%20in%20the%20%22date%22%20row%2C%20and%20have%20each%20days%20date%20automatically%20be%20in%20the%20cell%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2026127%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2026147%22%20slang%3D%22en-US%22%3ERe%3A%20Entering%20todays%20date%20automatically%20when%20clicking%20on%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2026147%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F915574%22%20target%3D%22_blank%22%3E%40going156gmailcom%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExcel%20does%20not%20distinguish%20between%20clicking%20on%20a%20cell%20and%20moving%20to%20the%20cell%20using%20the%20keyboard%2C%20so%20entering%20the%20date%20when%20you%20click%20on%20a%20cell%20would%20imply%20that%20it%20won't%20be%20possible%20to%20visit%20the%20cell%20without%20entering%20the%20date.%20I'd%20use%20double-click%20instead%20of%20click.%3C%2FP%3E%0A%3CP%3ERight-click%20the%20sheet%20tab%20and%20select%20'View%20Code'%20from%20the%20context%20menu.%3C%2FP%3E%0A%3CP%3ECopy%20the%20following%20code%20into%20the%20worksheet%20module%2C%20adjusting%20it%20as%20indicated.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_BeforeDoubleClick(ByVal%20Target%20As%20Range%2C%20Cancel%20As%20Boolean)%0A%20%20%20%20If%20Not%20Intersect(Range(%22B2%3AZ2%22)%2C%20Target)%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20%20%20%20%20Target.Value%20%3D%20Date%0A%20%20%20%20%20%20%20%20Cancel%20%3D%20True%0A%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20True%0A%20%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ESwitch%20back%20to%20Excel.%3C%2FP%3E%0A%3CP%3ESave%20the%20workbook%20as%20a%20macro-enabled%20workbook%20(.xlsm).%3C%2FP%3E%0A%3CP%3EMake%20sure%20that%20you%20allow%20macros%20when%20you%20open%20the%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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

1 Reply

@going156gmailcom 

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.