Auto update a date

%3CLINGO-SUB%20id%3D%22lingo-sub-2143196%22%20slang%3D%22en-US%22%3EAuto%20update%20a%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2143196%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20do%20I%20have%20a%20cell%20auto%20update%20with%20the%20current%20date%20whenever%20a%20value%20in%20its%20row%20changes%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EDave%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2143196%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2143273%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20update%20a%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2143273%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F969664%22%20target%3D%22_blank%22%3E%40maceslin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20that%20you%20want%20the%20current%20date%2Ftime%20in%20column%20M%20whenever%20a%20cell%20in%20columns%20A%20to%20L%20is%20changed%20by%20the%20user.%3C%2FP%3E%0A%3CP%3ERight-click%20the%20sheet%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20'View%20Code'%20from%20the%20context%20menu.%3C%2FP%3E%0A%3CP%3ECopy%20the%20following%20code%20into%20the%20worksheet%20module%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20%20Dim%20rw%20As%20Range%0A%20%20%20%20If%20Not%20Intersect(Range(%22A%3AL%22)%2C%20Target)%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20%20%20%20%20For%20Each%20rw%20In%20Intersect(Range(%22A%3AL%22)%2C%20Target).Rows%0A%20%20%20%20%20%20%20%20%20%20%20%20Range(%22M%22%20%26amp%3B%20rw.Row).Value%20%3D%20Now%0A%20%20%20%20%20%20%20%20Next%20rw%0A%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20True%0A%20%20%20%20%20%20%20%20Application.ScreenUpdating%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
New Contributor

How do I have a cell auto update with the current date whenever a value in its row changes

Thanks

Dave

5 Replies

@maceslin 

Let's say that you want the current date/time in column M whenever a cell in columns A to L is changed by the user.

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rw As Range
    If Not Intersect(Range("A:L"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rw In Intersect(Range("A:L"), Target).Rows
            Range("M" & rw.Row).Value = Now
        Next rw
        Application.EnableEvents = True
        Application.ScreenUpdating = 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.

@Hans Vogelaar 

Works perfectly until I upload it to Google Docs for sharing with others.  It appears Google docs does not support macro-enabled workbooks.  Any other ideas

@maceslin 

VBA indeed doesn't work on the web. I don't know of a solution for that, sorry.

@Hans Vogelaar   thanks for the help

 

I know it won't help the OP, but if it's possible the user will be adding/deleting a good number of cells at one time, you could cut down on the macro run time by addressing column M as one range instead of one cell at a time. 

 

Private Sub Worksheet_Change(ByVal target As Range)
    If Not Intersect(Range("A:L"), target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        Intersect(Intersect(Range("A:L"), target).EntireRow, Range("M:M")).Value = Now
        
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub