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

C

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
 
G

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
 
C

Colin Hayes

Gord Dibben said:
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
Click to expand...

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.
 
G

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
 
C

Colin Hayes

Gord Dibben said:
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
Click to expand...


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
 
G

Gord Dibben

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

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
 
C

Colin Hayes

Gord Dibben said:
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
Click to expand...


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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Fixing time of last update 2
Complex date VBA - insert a certain weekday 5
Date inconsistent help please :) 3
Displaying when workbook was last updated 4
Excel Macro to copy a row to another sheet based on date 0
cell for date last updated 1
Display date when worksheet last updated? 1
convert 13.11.2009 general to 13/11/2009 date, how to 1

Top