Forum Discussion
Auto update a date
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 SubSwitch back to Excel.
Save the workbook as a macro-enabled workbook (.xlsm).
Make sure that you allow macros when you open the workbook.
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 SubWorks 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
VBA indeed doesn't work on the web. I don't know of a solution for that, sorry.
HansVogelaar thanks for the help
