Need to record a timestamp for every time EVERY cell in a table is updated

Joined
Jan 14, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi,

Need some help!

I am running this VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B3:B23")) Is Nothing Then Exit Sub
Range("K" & Target.Row) = Now
End Sub

But!

I also need to run this code for range C3:C23 and D3:D23 and E3:E23 etc. etc.
and place the NOW timestamps in columns L, M, and N and so on and so forth respectively

So basically, I have a table where each cell is updated on a weekly basis and I need a mirrored table to tell me when each of the corresponding cells in the other table was last updated. See attached example tables.

Thanks in advance!
 

Attachments

  • mrexcel2.PNG
    mrexcel2.PNG
    74.2 KB · Views: 11

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

rollis13

Active Member
Joined
Jul 30, 2012
Messages
426
Office Version
  1. 2016
Platform
  1. Windows
Have a try with these changes. I also added 'Application.EnableEvents" to avoid redundancy (triggering event Worksheet.Change once again when you paste 'Now'):
VBA Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B3:B23,C3:C23,D3:D23,E3:E23,F3:F23,G3:G23,H3:H23,I3:I23")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Cells(Target.Row, Target.Column).Offset(0, 9) = Now
    Application.EnableEvents = True
End Sub
 
Last edited:
Solution
Joined
Jan 14, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Have a try with these changes. I also added 'Application.EnableEvents" to avoid redundancy (triggering event Worksheet.Change once again when you paste 'Now'):
VBA Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B3:B23,C3:C23,D3:D23,E3:E23,F3:F23,G3:G23,H3:H23,I3:I23")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Cells(Target.Row, Target.Column).Offset(0, 9) = Now
    Application.EnableEvents = True
End Sub

THANK YOU VERY MUCH! Now I can hold people accountable for updating their stuff!
 

rollis13

Active Member
Joined
Jul 30, 2012
Messages
426
Office Version
  1. 2016
Platform
  1. Windows
Glad I was able to help (y).
 
Joined
Jan 14, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
So....another follow up on this. The script works nicely when I edit the shared workbook but, I can't seem to get this script to trigger when other users edit the file--which is kind of the whole point.
I have troubleshooted the situation (made sure they are on the desktop version and made sure that all macros are enabled in the trust center). It still wont put a time stamp when they edit... Only when I edit, will it work.

Thoughts?
 

rollis13

Active Member
Joined
Jul 30, 2012
Messages
426
Office Version
  1. 2016
Platform
  1. Windows
How do users 'get' to the file ? If it's not via local network then probably that's the problem. In most cases when macros are shared via web these won't work. Only if the file is locally available (downloaded) as you have noticed the macros will be operative.
Sorry, no other thought, it's a very simple macro.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,089
Messages
5,640,052
Members
417,124
Latest member
Herostrata

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top