Date Sheet Last Updated, and By Whom

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
102
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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! :unsure:

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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub
 
Solution

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
102
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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

Board Regular
Joined
Sep 29, 2015
Messages
102
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Sep 29, 2015
Messages
102
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
You're code works happily for me. What happens if you close Excel completely & then reopen it?
 

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
102
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,592
Messages
5,637,290
Members
416,962
Latest member
samfuge

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