Solved

Record date and time in when data changed in a cell

Posted on 2010-09-21
15
254 Views
Last Modified: 2012-05-10
I have a row of cells into which I manually input data. I want to add a cell at the end of the row that shows the date and time when any of the cells was last updated.

They are prices from various suppliers and I want to know how current the pricing is.
0
Comment
Question by:rduval
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 2
  • +1
15 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 33726921
If you had data in A1 to F1 you could try something like this in the sheet module.
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A1:F1")) Is Nothing Then Exit Sub

Range("G1") = Now()

End Sub

Open in new window

0
 
LVL 14

Expert Comment

by:sungenwang
ID: 33726926
You can use this VBA solution:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=863

sew


0
 
LVL 3

Expert Comment

by:JefKve
ID: 33726938
Add this to the worksheet where you will be making cahnges as modify as necessary...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer
'x x will hold how many columns over we want ot place the change date
x = 2
Target.Offset(0, x) = Now()
End Sub
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:rduval
ID: 33726947
Thanks for the quick comebacks guys but I have about a thousand rows and, unless I'm mistaken, I'd need a thousand pcs of these codes wouldn't I?
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 33726958
Something like this?
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A1:F1000")) Is Nothing Then Exit Sub

Cells(Target.Row, "G") = Now()

End Sub

Open in new window

0
 
LVL 3

Expert Comment

by:JefKve
ID: 33727018
No you wont as its looking anywhere in the sheet for the change, not just at a single row/cell....
0
 

Author Comment

by:rduval
ID: 33727069
Sorry but I'm not much of an Excel Techie. I can see that a couple of solutions a working here. sungenwangs test files seem to do what I need but I'm a little confused as to how to implement it.

If I'm using:

Sub SetDateRow(Target As Range, Col As String)
    If Target.Cells.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    Cells(Target.Row, Col) = Now()
    Application.EnableEvents = True
End Sub

Do I define named ranges?

HOw do I target say, A1:F1000 and have the date written to A10 for the first row, etc.?



0
 
LVL 24

Expert Comment

by:StephenJR
ID: 33727074
Why not post a sample workbook and we can give specific advice.
0
 

Author Comment

by:rduval
ID: 33727137
Here's the file from the link that sungenwang sent.

If using the first sheet, I'm not understanding how the vb code is targeting the first 3 cells?

I think the code "SetDateRow Target, "D" is telling the macro to put the date in the D Row is that right?
TestDate.zip
0
 

Author Comment

by:rduval
ID: 33727157
I guess I need to know how to target cells W10:Z1000 for instance
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 33727158
You could just replace current code with this. Date/time goes in D whenever A-C changed.
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column > 3 Then Exit Sub

Cells(Target.Row, "D") = Now()

End Sub

Open in new window

0
 
LVL 24

Expert Comment

by:StephenJR
ID: 33727168
My code is effectively the same as the code you had, so you may already have solved the problem! But I don't see how your reference to W10:Z1000 fits with your attachment?
0
 

Author Comment

by:rduval
ID: 33727264
I only want to update the date when a value changes within a certain range of cells.

In my case I want to put the date in AI whenever anything changes in AC to AH.

I changed the code to SetDateRow Target, "AI" which puts the date in AI but when any vallue in any column changes then the date changes. I have other data in other columns and I don't want the date updating when that other data changes, only when the data in AC-AH changes.


0
 
LVL 24

Accepted Solution

by:
StephenJR earned 500 total points
ID: 33727282
Try this.
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("AC:AH")) Is Nothing Then Exit Sub

Cells(Target.Row, "AI") = Now()

End Sub

Open in new window

0
 

Author Closing Comment

by:rduval
ID: 33727324
Bingo! Thanks all
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question