PDA

View Full Version : Solved: put current date in a column if any change in row



sreejith
02-19-2010, 03:42 PM
A newbie to excel and wondering how to do this. I have an excel sheet with say data in column A to F. What I want is if I change the value of any column from A to F, column H should be set to current date automatically.

Any help much appreciated

Sreejith

GTO
02-19-2010, 03:58 PM
Greetings Sreejth,

Welcome to vbaexpress! :thumb

Are you asking for help with a macro/vba code? Presuming yes for the moment, do you mean that if any of the cells' values in a given row (from columns A - F) are changed by the user (vs. calculation) would result in the date being updated in H(whatever row)?

Mark

sreejith
02-19-2010, 04:02 PM
Mark,
Thanks. Yes that's what I am looking for.

I got a piece of code from this site which will update the comment and tried to hack it. But this fills in column H of all the rows. How do I just update the row I changed.



Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Columns("H") = "Previous Value was " & preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName")
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target = "" Then
preValue = "a blank"
Else: preValue = Target.Value
End If
End Sub

SamT
02-20-2010, 07:14 AM
Try this


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim H as Long
H = 8 'Column number of "H"
If Target.Count > 1 Then Exit Sub
Cells(Target.Row, H) = Now
End Sub



You'll have to put in some checks to make sure that "Target" is within the range of cells you're watching.

SamT

lucas
02-20-2010, 08:17 AM
Adding a few Checks to Sam's code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim H As Long
H = 8 'Column number of "H"
If Target.Count > 1 Then Exit Sub 'if more than one cell selected
If Target.Row = 1 Then Exit Sub 'do nothing on header row
If Target.Column >= 1 And Target.Column <= 6 Then ' if in column A -F then add date
Cells(Target.Row, H) = Now
Else
Exit Sub
End If
End Sub

sreejith
02-20-2010, 11:50 AM
Sam/lucas
Thanks very much for your help. Worked perfectly

Thanks
Sreejith

lucas
02-20-2010, 11:57 AM
Sreejith, be sure to mark your thread solved using the thread tools at the top of the page.

That way others won't read the entire thread to find that it's been solved.

sreejith
02-20-2010, 02:26 PM
Lucas,
Thanks for the reminder.

sreejith

SamT
02-20-2010, 10:01 PM
Thank you Lucas.

I had just gotten that little bit tested when SWMBO said, "We're leaving."

Yipes!

To make it a little more self documenting:


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Date_Column As Long
Date_Column = 8 'Column number of "H"
Dim Header_Row As Long
Header_Row = 1 'Number of bottom row of headers


If Target.Count > 1 Then Exit Sub 'if more than one cell selected
If Target.Row <= Header_Row Then Exit Sub 'do nothing on header row
If Target.Column >= 1 And Target.Column <= 6 Then ' if in column A -F then add date
Cells(Target.Row, Date_Column) = Now
Else
Exit Sub
End If
End Sub


SamT

lucas
02-21-2010, 08:35 AM
No wonder you were scrambling.

Nice add on the code too. Makes Multiple header rows easy to deal with too.

SamT
02-21-2010, 12:02 PM
I always want to be able to come back in a year and understand what's going on at first reading.

SamT