Automatic datestamping with VBA

[Zuomine]

Hi all,

I've been trying to find a way to automatically generate datestamps for my worksheet. I would like to have a datestamp automatically update in Column W when any of the data on Columns D-O is changed. For example, when user changes data in E12 today, W12 states 20/09/2023. When a user updates data in L14 tomorrow, W14 states 21/09/2023 and so on. I have 30+ rows that I need to use this on. Column W is also ideally protected, so if there's a workaround for that, that would be awesome.

What's the best and most future proof way to approach this?

Thank you for your help in advance!

 

[Joe4]

Welcome to the Board!

You will need to use VBA in order to do this.

Go to the sheet you wish to apply this to, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
'   Watched range D12:O41
    Set rng = Intersect(Target, Range("D12:O41"))
    
'   Exit if update not made in watched range
    If rng Is Nothing Then Exit Sub

'   Unprotect sheet and disable events
    ActiveSheet.Unprotect Password:="password1"
    Application.EnableEvents = False
    
'   Loop through update cells and update timestamp in column W
    For Each cell In rng
        Cells(cell.Row, "W").Value = Date
    Next cell
    
'   Re-enable events and re-protect sheet
    Application.EnableEvents = True
    ActiveSheet.Protect Password:="password1"
    
End Sub

You will need to change two things in this code:
- the references to range D12:O41 (change this to the range of the cells that you want to trigger the datestamp to run)
- the word "password1" to the password you have used to protect this sheet

The code will automatically add the date stamp to column W as you update a cell in your watched range.

 

[Zuomine]

Welcome to the Board!

You will need to use VBA in order to do this.

Go to the sheet you wish to apply this to, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
   
'   Watched range D12:O41
    Set rng = Intersect(Target, Range("D12:O41"))
   
'   Exit if update not made in watched range
    If rng Is Nothing Then Exit Sub

'   Unprotect sheet and disable events
    ActiveSheet.Unprotect Password:="password1"
    Application.EnableEvents = False
   
'   Loop through update cells and update timestamp in column W
    For Each cell In rng
        Cells(cell.Row, "W").Value = Date
    Next cell
   
'   Re-enable events and re-protect sheet
    Application.EnableEvents = True
    ActiveSheet.Protect Password:="password1"
   
End Sub

You will need to change two things in this code:
- the references to range D12:O41 (change this to the range of the cells that you want to trigger the datestamp to run)
- the word "password1" to the password you have used to protect this sheet

The code will automatically add the date stamp to column W as you update a cell in your watched range.

This is spot on, perfect. Thank you for the quick reply!

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[Zuomine]

You are welcome.
Glad I was able to help!

Hi,

For whatever reason, the script worked fine a few times. After that it gave me an error (I dismissed it too quick unfortunately) that said something about the protected cells. Now the scripts refuses to work completely. Is there a way to reset the code?

 

[Joe4]

It sounds like the code ran the line that disables events, but the one that turns them back on did not run, so your code is no longer firing automatically.

Manually run this one line procedure to re-enable events, and it should work again:

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

 

[Zuomine]

It sounds like the code ran the line that disables events, but the one that turns them back on did not run, so your code is no longer firing automatically.

Manually run this one line procedure to re-enable events, and it should work again:

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

This seems to have fixed it. I'll save this bit of code for further use. Huge thanks Joe4!

 

[Joe4]

You are welcome.

Note it is important to understand what this is doing.
"Event procedures" are VBA procedures that run automatically upon some "event" happening, such as the updating of a cell, a calculation happening, the opening of a file, the saving of a file, etc. In order for them to happen automatically, there are very strict guidelines around how they are named, and where the code is to be placed.

However, we have to be careful with these automated codes, or else we can easily get caught in an infinite loop. For example, if we have code that runs automatically when a cell is updated, but the code itself is updating cells, it can cause the code to call itself and get caught in an inifite loop (not fun, it is hard to stop the code!).

The way we get around that is in our VBA code, we temporarily suspend events from running (so no automation happens when we are updating our cells).
That is the line of code that looks like this:

    Application.EnableEvents = False

Then, after the updates are completed, we turn them back on so automation can happen again, like this:

    Application.EnableEvents = True

So if your code gets interrupted somewhere between those two lines, automation is off and the code won't fire. That is why we needed to run that one line of code to turn it back on.
Alternatively, you could close Excel and re-open it to reset those settings.

Hope that all makes sense!