Date Sheet Last Updated, and By Whom

[SaraWitch]

Hello fellow Excel peeps!

I want to record the date an Excel sheet was updated in cell G1. So far I have tried:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("G1") = Now()
Application.EnableEvents = True
End Sub

and:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row = 2 Then
ActiveSheet.Cells(7, 1).Value = TODAY()
End If
End Sub

and:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim cell As Range
Set rng = Intersect(Target, Columns("A:T"))
If rng Is Nothing Then Exit Sub
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each cell In rng
Cells(cell.Row, "2") = Now()
Next cell
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

But none are working for me and I can't work out what I'm doing wrong!

And if I can add who last updated the sheet in H1, that would grand!

Using Excel 365....

Any help would be appreciated as need to finish the spreadsheet today!

Ta muchly, folks!
Sara

 

[Joe4]

If you wanted to record the change to ANY cell on the worksheet, the first code would be best to do that.

Note a few things that in order to get this to work:
1. That code MUST be in the Sheet module that you want to apply it to, not to a General/Standard Module.
2. VBA/Macros must be enabled for it to work.
3. Events must NOT be disabled for it to work.

Regarding the 3rd item, events are enabled by default, but if you have VBA code that temporarily disables them, and then gets interrupted before it re-enables them, it will prevent your code from working.

You can re-enable them by manually running this short macro:

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

 

[SaraWitch]

Hi Joe4,

Thank you for speedy reply!

The code is in the Sheet module only, the workbook is macro-enabled, and I have added your VBA (to the Sheet and General module, just in case), but it still isn't working for me...? I've saved the workbook, closed it down, gone back into it, made changes to the sheet, but the cell remains blank...? I don't have to put a formula in G1, do I (I don't think so, but am a bit stumped!)?

Any ideas?

Thanks...

 

[Joe4]

No, it should all be automated.
Are you sure that you put the code in the correct Worksheet module (there may be multiple ones)?

Do you have any other VBA code in your workbook?
If so, can you post it?

Make this update to the procedure:

Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox "VBA is being called"
    Application.EnableEvents = False
    Range("G1") = Now()
    Application.EnableEvents = True
End Sub

Then manually run the other procedure I gave you first.
Then, make a change to any cell on the worksheet.
What happens?
Do you get the message box pop-up?

 

[SaraWitch]

Nope; nothing is working (I even tested it in a blank workbook, and no joy!)... Changed VBA, no pop-up message and VBA definitely in correct sheet (there are 13 sheets; sheet 13 is where I want to see when last updated and this is where the code is).

Only other VBAs I have are in general Module 1, which are assigned as macros to buttons in another sheet, which are working - three (e.g. Rectangle1, 2 and 3):

Sub RectangleRounded1_Click()
ActiveSheet.Range("SixMonthly").Select
End Sub

I'd be happy to attach the workbook, but can't find a way of doing that...?

 

[Joe4]

I'd be happy to attach the workbook, but can't find a way of doing that...?

Just upload the file to any file sharing site (like Dropbox) and provide the link for us to download.

 

[SaraWitch]

Bit of a novice with sharing sites, but does this work (OneDrive)...?
Compliance Check Log V2.xlsm

I need the updated date VBA for the 'Handover' Sheet (Sheet 13)...

 

[Fluff]

You're code works happily for me. What happens if you close Excel completely & then reopen it?

 

[SaraWitch]

Oh my word! Was it really as easy as 'is it plugged in'?

It's working! (I realised though that the cell (G1) has to be unlocked for the formula to work, otherwise it returns an error.)

Thank you both for your time and patience!

Enjoy the weekend

 

[Joe4]

You're code works happily for me. What happens if you close Excel completely & then reopen it?

Oh my word! Was it really as easy as 'is it plugged in'?

Usually, when that happens, it is because events were disabled, and running that manual code I asked you to run should correct that.
Were you unable (or unsure) on how to do that?