How to auto-enter a date for when your sheet was last updated

[Colin Hayes]

Hi

I update my worksheet with data from the Internet by clicking the
'Refresh All' icon.

What I need to do is to have a cell show the date (dd/mm/yyyy) when the
sheet was last updated , before I go to get the new data.

Can someone help with this?


Grateful for any advice

Best Wishes

 

[Gord Dibben]

Right-click on the sheet tab and "View Code" to open module.

Copy/paste this event code into the sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("A1")
'edit to suit. chosen cell must be within refreshed data range
If .Value <> "" Then
Me.Range("D1").Value = Format(Now, "dd-mm-yy hh:mm:ss")
'edit "D1" to suit where you want the date/time entered
End If
End With
stoppit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[Colin Hayes]

Right-click on the sheet tab and "View Code" to open module.

Copy/paste this event code into the sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("A1")
'edit to suit. chosen cell must be within refreshed data range
If .Value <> "" Then
Me.Range("D1").Value = Format(Now, "dd-mm-yy hh:mm:ss")
'edit "D1" to suit where you want the date/time entered
End If
End With
stoppit:
Application.EnableEvents = True
End Sub

Hi Gord

OK thanks for getting back.

No joy , I'm afraid.

I assume you need me to change A1 in the code to one the cells in my
sheet which is being refreshed , and I wanted the date to be entered in
B16.

I tried modifying the code accordingly , but I kept getting errors. My
fault I'm sure. When I refreshed , I lost all figures in the sheet to
question marks and value errors. I'll keep trying.


Best Wishes.

 

[Gord Dibben]

The code will not produce the question marks and value errors when you refresh
data.

What happens when you refresh data on your original sheet without the code?

Simply editing "A1" to another cell should not produce any errors within the
code.

Make sure "B16" is out of refresh range.

Did you for sure add the code to the sheet module?

What type of errors does the code throw up?

Can you post your attempt at amended code?


Gord

 

[Colin Hayes]

The code will not produce the question marks and value errors when you refresh
data.

What happens when you refresh data on your original sheet without the code?

Simply editing "A1" to another cell should not produce any errors within the code.

Make sure "B16" is out of refresh range.

Did you for sure add the code to the sheet module?

What type of errors does the code throw up?

Can you post your attempt at amended code?


Gord

Hi Gord

Thanks for getting back.

I've since found that the question marks were caused by a server issue
at
the site I'm updating from , so that's not from the code.

When I refresh the data without the code , it updates the numbers it
fetches from the internet as it should.

I'm changing A1 in the code to D4 , which is one of the cells that will
be
updated on refresh.

B16 is a blank cell on the sheet where I want the date / to be placed.

I'm putting the code under the tab on the sheet I'm refreshing.

When I refresh with the code in place , the numbers do refresh as they
should , but nothing is showing in B16.

Here's the code I'm putting in :

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("D4")
'edit to suit. chosen cell must be within refreshed data range
If .Value <> "" Then
Me.Range("B16").Value = Format(Now, "dd-mm-yy hh:mm:ss")
'edit "D1" to suit where you want the date/time entered
End If
End With
stoppit:
Application.EnableEvents = True
End Sub


For your interest , I've put the file itself here :

http://www.chayes.demon.co.uk/ShareSample.xls



Best Wishes

 

[Gord Dibben]

When I refresh with the code in place , the numbers do refresh as they

should , but nothing is showing in B16.

ShareSheet is protected so changes to B16 cannot be made.

Because I error-trapped the code, you don't see an error message informing you
of that fact.

Also, the data refresh is actually done on LookUpData sheet and ShareSheet D4 is
only a reference to D4 on that sheet,

We could change the event type to Calculate but that would also update B16 when
VirtualLookUpData is refreshed..............not desirable, I would say.

Delete the code you have in ShareSheet

The following amended code goes into module of LookUpData sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Me.Range("D4").Value <> "" Then
With Sheets("ShareSheet")
.Unprotect Password:="justme"
.Range("B16").Value = Format(Now, "dd-mm-yy hh:mm:ss")
stoppit:
Application.EnableEvents = True
.Protect Password:="justme"
End With
End If
End Sub

The code unprotects ShareSheet so the date/time can be added to B16

I used a cracker to unprotect ShareSheet for testing the code..

Works fine for me when I refresh data on LookUpData sheet.

Edit password "justme" to your actual password.


Gord

 

[Colin Hayes]

ShareSheet is protected so changes to B16 cannot be made.

Because I error-trapped the code, you don't see an error message informing you of
that fact.

Also, the data refresh is actually done on LookUpData sheet and ShareSheet D4 is
only a reference
to D4 on that sheet,

We could change the event type to Calculate but that would also update B16 when
VirtualLookUpData is refreshed..............not desirable, I would say.

Delete the code you have in ShareSheet

The following amended code goes into module of LookUpData sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Me.Range("D4").Value <> "" Then
With Sheets("ShareSheet")
.Unprotect Password:="justme"
.Range("B16").Value = Format(Now, "dd-mm-yy hh:mm:ss")
stoppit:
Application.EnableEvents = True
.Protect Password:="justme"
End With
End If
End Sub

The code unprotects ShareSheet so the date/time can be added to B16

I used a cracker to unprotect ShareSheet for testing the code..

Works fine for me when I refresh data on LookUpData sheet.

Edit password "justme" to your actual password.


Gord

Hi Gord

OK I ran it and it works perfectly. Exactly the solution to the problem.

Thanks very much indeed - I'm grateful to you for your time and
expertise.


Best Wishes

 

[Gord Dibben]

Happy to oblige


Gord