Show date last modified for any cells in row

Microsoft Excel 2003 (full product)
January 6, 2011 at 08:15:20
Specs: Windows XP
In Excel 2003, I am trying to get the date of a cell in Column R to update when I modify any cells in the row (columns A-Q). Right now I have put this code in the "View Code" option when I right click on the worksheet tab (from this forum):

Private Sub Worksheet_Change(ByVal Target As Range)
Range("R" & Target.Row) = Date
End Sub

I then close the code popup window, save the design worksheet which appears and returned to my spreadsheet, test this and it doesn't work even though the code appears when right clicking the worksheet tab and then choosing view code. What am I doing incorrectly?

See More: Show date last modified for any cells in row

January 6, 2011 at 13:06:14
It's not clear to me what sheet tab you are clicking on but any Worksheet event code has to be placed in the sheet module for the sheet (and every sheet) where you want this to happen.

Worksheet event macros are not global, they are Worksheet specific.

If you want the date to appear in Sheet1 Column R, then you need to right-click the Sheet1 tab and paste code into the pane that opens.

The only other reason that I can think for the code not to work for you (it works for me) is if you have some code that has turned events off.

If the line:

Application.EnableEvents = False

has been run in any macro while the current Excel session is open, then no event code will fire since the execution of that line impacts the application, i.e. Excel.

Quitting Excel and restarting it or running the following line will enable events again:

Application.EnableEvents = True

Note: if you have a Macro that runs the "False" line at Excel start-up, then even quitting and restarting Excel won't help since the code will disable events as soon as Excel starts.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 10, 2011 at 12:34:09
Hi Derby Dad:

This was somewhat helpful. The code was put into only the first worksheet where I want the date to appear in column R. I then discovered that macros were disabled. So I closed down Excel thinking that would help, changed the security setting to low to enable macros. I then double checked macros in that worksheet for the "false" line you mentioned and there were no macros in this worksheet. How do I check for a macro containing the "false" line on the application? I went to "Tools", "Macro" and there does not appear to be any macros, but I can't tell for sure if there are application macros. Can you provide more assistance? I appreciate your help

Report •

January 10, 2011 at 13:09:54
If you don't see any macros listed under Tools...Macros, you might try double clicking the ThisWorkbook module in the VBA editor. It's worth a try, but I doubt you'll find anything there.

Have you tried any other macros?

Have you recorded a macro and then run it?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question