Excel - date row last updated 1

[Predator98]

Is there a way to record the last update (modified) date at the end of a row when "any" value on the row is changed? I currently have the following code but it updates the date any time the row entered:

Range ("BX" & Target.Row).Value = Date

where "BX" is where I want the date placed.

I cannot figure out an If statement to wrap around this. Any suggestions?

 

[SkipVought]

Please post VBA questions in Forum707.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cckens]

VB code will do it, but you have to set it up correctly. You need to set up a set of code to check each range for changes. I'll give my example code, but this should probably be posted to the VBA forum.

Private Sub Worksheet_Change(ByVal Target As range)
If Not Intersect(Target, range("e3:e806")) Is Nothing Then
Dim curr As String 'dim current value of $F cell
Dim newnm As String 'dim value of username
Dim actcell As String 'dim initial cell address
newnm = Environ("UserName") 'set value to username
Worksheets("smartlist").Activate
actcell = Target.Address 'set value to inital address
Worksheets("smartlist").range(actcell).Activate
ActiveCell.Offset(0, 1).Activate
curr = ActiveCell.Value
curr = curr & "," & newnm
ActiveCell.Value = curr
ActiveCell.Offset(1, -1).Activate
End If
End Sub

What this did was check the range for changes, then input the user's loginID into a hidden column, so I could track who made changes into the range. It concatenated entries so that I would have a running list of who made changes and in what order. A little crude, but it worked for what I needed it for. Simply sub the date in for the username, change the ranges to fit and you should be good to go.
Problems should be referred to the VBA forum, though.

Ken

"cckens is a nick... why the H-E- double-hockey-sticks am I using a nick for a name? Am I afraid of who I am?"
-me
"...don't know why, but I think of chickens when I see that nick...maybe even choking chickens???"
-Tony (wahnula)

 

[Predator98]

Sorry...new to this forum...thanks for the advice.

 

[Predator98]

Thanks...I posted it in the VBA forum after the reply from SkipVought

 

[Predator98]

Thanks, cckens. So far this is working great!

 

[SkipVought]

cckens,

Please refrain ffom posting VBA code in this forum. I had already posted to the OP to post in the VBA forum, and you had ample time to note this request.

I will not comment on the details of your code in this forum, other than to state that activating a different sheet, is not a particularly good coding method, as the same outcome can and should be had WITHOUT activating another sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cckens]

My apologies, Skip, and I did say it was a crude kind of procedure. But for what it was worth, I was explicitly activating the working sheet in a single sheet workbook (which you wounldn't have known) because it wouldn't work any other way I tried it. Not exactly prime coding, but like I said, it worked for me.

Add that to the fact that I was typing while you were posting, makes me the latecomer to the party.

Once again, apologies aplenty from me, and it won't happen again.

Ken

"cckens is a nick... why the H-E- double-hockey-sticks am I using a nick for a name? Am I afraid of who I am?"
-me
"...don't know why, but I think of chickens when I see that nick...maybe even choking chickens???"
-Tony (wahnula)