Last Updated Date of a row

[shwpew]

I'm looking to populate a field with the last date a row was updated. Currently, I have data in columns A - J and if any of those are updated for a given row, I would like column K of that row to be updated with that date. (I'm fine with date and time as well if that is easier). This is a spreadsheet of contact information and I want to be able to easily tell the last time I (or any user of the spreadsheet) updated the contact information for a given entry.

I have tried:

[FONT=&quot]Private Sub Worksheet_Change(ByVal Target As Range)[/FONT]
[FONT=&quot]If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub[/FONT]
[FONT=&quot]If Not Intersect(Target, Columns("B:D")) Is Nothing Then[/FONT]
[FONT=&quot] On Error Resume Next[/FONT]
[FONT=&quot] Application.EnableEvents = False[/FONT]
[FONT=&quot] Cells(Target.Row, 7) = Now[/FONT]
[FONT=&quot] Cells(Target.Row, 7).EntireColumn.AutoFit[/FONT]
[FONT=&quot] Application.EnableEvents = True[/FONT]
[FONT=&quot] On Error GoTo 0[/FONT]
[FONT=&quot] End If[/FONT]
[FONT=&quot]End Sub
[/FONT]​

[FONT=&quot]
**AND**

[/FONT]

Private Sub Worksheet_Change(ByVal Target As Range)​

Application.EnableEvents = False​

Cells(Target.Row, "A").Value = Now()​

Application.EnableEvents = True​

End Sub​

[FONT=&quot]But get an error of "Argument Not Optional"

I have tried

[/FONT]

Private Sub Worksheet_Change(ByVal Target As Range)​

​

Application.EnableEvents = False​

Range("E7") = Now()​

Application.EnableEvents = True​

​

End Sub​

And it works, but it updates cell E7 any time any cell in the entire document is updated.

Can someone help me modify the code that worked, so that it has the limitations I require? If that's not possible, can you provide me step by step instructions of what I need to make this work? (Commented out details of what each line of code is actually doing a bonus.)

This is my first attempt at VBA so step by step instructions are greatly appreciated.

THANKS!

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Intersect(Target, Range("A:J")) Is Nothing Then Exit Sub
   Application.EnableEvents = False
   Range("K" & Target.row).Value = Date
   Application.EnableEvents = True
   
End Sub

Using Date will insert the current date & will remain unaltered

 

[shwpew]

Thank you Fluff. I am no longer getting the "Argument Not Optional" error, but nothing is populating in column K when I update cells in other columns (A - J).

Any thoughts on that?

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Intersect(Target, Range("A:J")) Is Nothing Then Exit Sub
   Application.EnableEvents = False
   Range("K" & Target.row).Value = Date
   Application.EnableEvents = True
   
End Sub

Using Date will insert the current date & will remain unaltered

 

[Fluff]

Have you put the code in the the relevant sheet module?

 

[shwpew]

I do ALT F11 to get the screen to come up.
Then I paste in the code.
I save and close everything out.
Then I re-open the document and update a cell, but nothing populates in K.

I followed these same steps for the previous code that worked for me and did see a date populate.

Am I missing a step? Thanks!!

 

[Fluff]

Right click on the sheet tab you want this to work on, select View Code. A code window should appear, is the code I supplied in there?
Also you need to ensure that there are no other Worksheet_Change events in the code module.

 

[shwpew]

Yes, the code appears when I right click on the tab and choose "view code"

There are no other "worksheet_change" events as far as I can tell.

I should be able to enter a value into any cell in columns A through J and the cell in column K of the same row should have the date populate, correct?

 

[shwpew]

If I close out of the document and follow the steps below, I'm now getting "Argument Not Optional"


1) Open the document
2) Populate information in random cells in columns A - J
3) Choose "view"
4) Choose "Macros"
5) Type in "worksheet_change"
6) Type "Run"

I have not touched the actual code provided. It still reads:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:J")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("K" & Target.Row).Value = Date
Application.EnableEvents = True

End Sub

 

[Fluff]

You cannot run a change sheet event from the VB editor, which is why you are getting that error.
Put this in a normal module, run it & then goto the sheet & enter something in col A, does the date appear in col K?

Sub chk()
Application.EnableEvents = True
End Sub

 

[shwpew]

Thank you for your patience, Fluff. I'm still not getting what I need.
When I create a new module and put what you listed above, I am able to run the "chk" module; but I am still not able to run the "worksheet_change" module. I get an error "Ambiguous name detected: Worksheet_Change" error. This is when I try a new worksheet as a whole.

If I do it in the spreadsheet I have already created, I still get the "Argument Not Optional" message.

Additionally, worksheet_change does not come up as an pre-populated macro to run when I choose run, only "chk" does. I have to write in "worksheet_change" in order to even attempt to run it.