Last updated time for cell

  • Thread starter Lawrence M. Seldin, CMC, CPC
  • Start date
L

Lawrence M. Seldin, CMC, CPC

I have looked in this newsgroup, however, I am new to Excel.

I have cell that gets automated by a data feed.

I want to know, when a cell has last been updated.

For instance. Cell +A1 has value 10 at 10:15am.
At 10:16am, it is updated with value of 11.

I want another cell to display the timestamp that cell +A1 was updated.

I am sorry, because I searche Excel 2000 help files and could not find how to do this simple thing.

Isn't there a simple function to do this?

Thanks much,


Lawrence M. Seldin, CMC, CPC
Author of POWER TIPS FOR THE APPLE NEWTON and INTRODUCTION TO CSP
Author of RECRUITSOURCE PEOPLESOFT EXAM and RECRUITSOURCE SAP/R3 EXAM


NOTE: To send me an email, remove TAKEOUT from my email address: (e-mail address removed)

NOTE: My web home page: www.seldin.net
 
B

Bob Phillips

Lawrence,

No function, but you could add event code to monitor C1 and add the time it
gets changed.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("C1")) Is Nothing Then
Me.Range("H1").Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
L

Lawrence M. Seldin, CMC, CPC

Can you pleae let me know how to add an event code. I did not see this in the Excel Help file.

Thanks much,

- Larry


Bob Phillips said:
Lawrence,

No function, but you could add event code to monitor C1 and add the time it
gets changed.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("C1")) Is Nothing Then
Me.Range("H1").Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If

ws_exit:
Application.EnableEvents = True
End Sub
Click to expand...

Lawrence M. Seldin, CMC, CPC
Author of POWER TIPS FOR THE APPLE NEWTON and INTRODUCTION TO CSP
Author of RECRUITSOURCE PEOPLESOFT EXAM and RECRUITSOURCE SAP/R3 EXAM


NOTE: To send me an email, remove TAKEOUT from my email address: (e-mail address removed)

NOTE: My web home page: www.seldin.net
 
B

Bob Phillips

Larry,

On the worksheet that you want this event to happen, right-click the sheet
tab, select View Code, and then just paste it in.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
L

Lawrence M. Seldin, CMC, CPC

Bob,

I got your code to work. Thank you.


Is there a way, to change the code you gave me to point to range of cells.

Like the price I want monitored is A1..A10 and the updated value would be B1..B10

Thanks much,

--------------------- begin

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("C1")) Is Nothing Then
Me.Range("H1").Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If

ws_exit:
Application.EnableEvents = True
End Sub

------------------- end
Lawrence M. Seldin, CMC, CPC
Author of POWER TIPS FOR THE APPLE NEWTON and INTRODUCTION TO CSP
Author of RECRUITSOURCE PEOPLESOFT EXAM and RECRUITSOURCE SAP/R3 EXAM


NOTE: To send me an email, remove TAKEOUT from my email address: (e-mail address removed)

NOTE: My web home page: www.seldin.net
 
G

Gord Dibben

Larence

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Col B time will not change if data in Col A is edited
On Error GoTo enditall
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("a1:a10")) Is Nothing Then
n = Target.Row
If Excel.Range("A" & n).Value <> "" _
And Excel.Range("B" & n).Value = "" Then
Excel.Range("B" & n).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Gord Dibben Excel MVP
 
L

Lawrence M. Seldin, CMC, CPC

Gord

Worked great!!!!

Thanks much.

Have a great weekend.

Much appreciated for the help.

- Larry





-----------------------------------------------------------------------------------

Gord Dibben said:
Larence

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Col B time will not change if data in Col A is edited
On Error GoTo enditall
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("a1:a10")) Is Nothing Then
n = Target.Row
If Excel.Range("A" & n).Value <> "" _
And Excel.Range("B" & n).Value = "" Then
Excel.Range("B" & n).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Gord Dibben Excel MVP
Click to expand...

Lawrence M. Seldin, CMC, CPC
Author of POWER TIPS FOR THE APPLE NEWTON and INTRODUCTION TO CSP
Author of RECRUITSOURCE PEOPLESOFT EXAM and RECRUITSOURCE SAP/R3 EXAM


NOTE: To send me an email, remove TAKEOUT from my email address: (e-mail address removed)

NOTE: My web home page: www.seldin.net
 
L

Lawrence M. Seldin, CMC, CPC

Gord,

Can you please tell me how to rearm the trigger, so when it gets updated throughout the day,
I can keep track of it being updated.

Thanks much,

- Larry



--------------------------------------------------------------------------------------------------
Gord Dibben said:
Larence

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Col B time will not change if data in Col A is edited
On Error GoTo enditall
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("a1:a10")) Is Nothing Then
n = Target.Row
If Excel.Range("A" & n).Value <> "" _
And Excel.Range("B" & n).Value = "" Then
Excel.Range("B" & n).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Gord Dibben Excel MVP
Click to expand...

Lawrence M. Seldin, CMC, CPC
Author of POWER TIPS FOR THE APPLE NEWTON and INTRODUCTION TO CSP
Author of RECRUITSOURCE PEOPLESOFT EXAM and RECRUITSOURCE SAP/R3 EXAM


NOTE: To send me an email, remove TAKEOUT from my email address: (e-mail address removed)

NOTE: My web home page: www.seldin.net
 
S

Steve Garman

If I understand your question correctly, you can start again from
scratch by deleting the date/times already recorded, e.g. by selecting
b1:b10 and pressing the delete key.
 
L

Lawrence M. Seldin, CMC, CPC

If I understand your question correctly, you can start again from
scratch by deleting the date/times already recorded, e.g. by selecting
b1:b10 and pressing the delete key.
Click to expand...


Steve,


I need something more automatic. See during the day, several fields are updated through DDE (I think
that's what it is called), so I like to know that I have the most current data and that the cell
prices are up to date. So since prices are updated automatically though a data feed, I don't want to
have to "press key strokes" all day long. I need some kind of "trigger" that checks for changes,
however, that keeps checking periodically.


(e-mail address removed)


-----------------------------------------------------------------------------------------------
Steve Garman said:
If I understand your question correctly, you can start again from
scratch by deleting the date/times already recorded, e.g. by selecting
b1:b10 and pressing the delete key.
Click to expand...

Lawrence M. Seldin, CMC, CPC
Author of POWER TIPS FOR THE APPLE NEWTON and INTRODUCTION TO CSP
Author of RECRUITSOURCE PEOPLESOFT EXAM and RECRUITSOURCE SAP/R3 EXAM


NOTE: To send me an email, remove TAKEOUT from my email address: (e-mail address removed)

NOTE: My web home page: www.seldin.net
 
B

Bob Phillips

Using an explicit range when you have a target seems superfluous to put it
mildly

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
With Target
If .Value <> "" Then
.Offset(0,1).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
S

Steve Garman

A small change to Gord's code should allow B1 to be updated every time
A1 changes, if that's wat you want

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Col B time will now change if data in Col A is edited
On Error GoTo enditall
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("a1:a10")) Is Nothing Then
n = Target.Row
If Excel.Range("A" & n).Value <> "" Then _
Excel.Range("B" & n).Value = Format(Now, "dd mmm yyyy
hh:mm:ss")
End If
End If
enditall:
Application.EnableEvents = True
End Sub
 
L

Lawrence M. Seldin, CMC, CPC

Bob, Steve and Gord,

You guys are awesome!

Thanks for your help.


Lawrence M. Seldin, CMC, CPC
Author of POWER TIPS FOR THE APPLE NEWTON and INTRODUCTION TO CSP
Author of RECRUITSOURCE PEOPLESOFT EXAM and RECRUITSOURCE SAP/R3 EXAM


NOTE: To send me an email, remove TAKEOUT from my email address: (e-mail address removed)

NOTE: My web home page: www.seldin.net
 
L

Lawrence M. Seldin, CMC, CPC

Bob,

I had used the code for the last update function. However, the quirk, is that a DDE call
updated that cell. When the cell is updated, it does not trigger the update event.

Any suggestion?

Thanks much,


Lawrence M. Seldin, CMC, CPC
Author of POWER TIPS FOR THE APPLE NEWTON and INTRODUCTION TO CSP
Author of RECRUITSOURCE PEOPLESOFT EXAM and RECRUITSOURCE SAP/R3 EXAM


NOTE: To send me an email, remove TAKEOUT from my email address: (e-mail address removed)

NOTE: My web home page: www.seldin.net
 
B

Bob Phillips

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

Lawrence,

DDE updates are not seen as changes to the worksheet, so the event doesn't
fire.

The best I can suggest is that you change from the Change event to the
Calculate event. You can take insurance by setting a cell to reference one
of the update cells, and that should update when the DDE call populates the
other cells.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
L

Lawrence M. Seldin, CMC, CPC

DDE updates are not seen as changes to the worksheet, so the event doesn't
fire.
The best I can suggest is that you change from the Change event to the
Calculate event. You can take insurance by setting a cell to reference one
of the update cells, and that should update when the DDE call populates the
other cells.
Click to expand...

I put in the following code. I also have a cell reference the cell in an updated cell.

Could you please look at this code:


Private Sub Worksheet_Calculate()
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("I4:I10")) Is Nothing Then
With Target
If .Value <> "" Then
.offset(0, 1).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


I tried to also ad this code as follows: "(ByVal Target As Range)"]
This gave a compiler error.

Private Sub Worksheet_Calculate (ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("I4:I10")) Is Nothing Then
With Target
If .Value <> "" Then
.offset(0, 1).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Thanks much,


Lawrence M. Seldin, CMC, CPC
Author of POWER TIPS FOR THE APPLE NEWTON and INTRODUCTION TO CSP
Author of RECRUITSOURCE PEOPLESOFT EXAM and RECRUITSOURCE SAP/R3 EXAM


NOTE: To send me an email, remove TAKEOUT from my email address: (e-mail address removed)

NOTE: My web home page: www.seldin.net
 
B

Bob Phillips

Looking at this again, I don't think we can make it work.

Can you try something else? Link say Z4:Z10 to I4:I10, each one in turn.
Then use this event code

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("Z4:Z10")) Is Nothing Then
With Target
If .Value <> "" Then
.offset(0, -16).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

What I am trying is to force the DDE updates to also update the range
Z4:Z10. This will (might?) trigger the change event, and we trap the
secondary range, and insert the date from there. I can't test it as I don't
have the DDE updates.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Lawrence M. Seldin said:
DDE updates are not seen as changes to the worksheet, so the event doesn't
fire.
The best I can suggest is that you change from the Change event to the
Calculate event. You can take insurance by setting a cell to reference one
of the update cells, and that should update when the DDE call populates the
other cells.
Click to expand...

I put in the following code. I also have a cell reference the cell in an updated cell.

Could you please look at this code:


Private Sub Worksheet_Calculate()
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("I4:I10")) Is Nothing Then
With Target
If .Value <> "" Then
.offset(0, 1).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


I tried to also ad this code as follows: "(ByVal Target As Range)"]
This gave a compiler error.

Private Sub Worksheet_Calculate (ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("I4:I10")) Is Nothing Then
With Target
If .Value <> "" Then
.offset(0, 1).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Thanks much,


Lawrence M. Seldin, CMC, CPC
Author of POWER TIPS FOR THE APPLE NEWTON and INTRODUCTION TO CSP
Author of RECRUITSOURCE PEOPLESOFT EXAM and RECRUITSOURCE SAP/R3 EXAM


NOTE: To send me an email, remove TAKEOUT from my email address: (e-mail address removed)

NOTE: My web home page: www.seldin.net
Click to expand...
 
L

Lawrence M. Seldin, CMC, CPC

Can you try something else? Link say Z4:Z10 to I4:I10, each one in turn.
Then use this event code
Click to expand...

Bob,

Thanks for the help. I will try it.


Lawrence M. Seldin, CMC, CPC
Author of POWER TIPS FOR THE APPLE NEWTON and INTRODUCTION TO CSP
Author of RECRUITSOURCE PEOPLESOFT EXAM and RECRUITSOURCE SAP/R3 EXAM


NOTE: To send me an email, remove TAKEOUT from my email address: (e-mail address removed)

NOTE: My web home page: www.seldin.net
 

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

How do I reference a cell in another worksheet 4
Exponential Moving Average 10
How can I automatically run a macro every 1 minute or every 5 minuts 2
How to enter a range plus an additional cell in a function. 2
Paste Link in Excel greyed out 0
Help with VB Excel 2
Help with VB Call function from Excel 0
Paste Link is greyed out during Paste Special 0

Top