Excel - date row last updated 2

[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]

Put your code into the Worksheet_Change event, rather than the Worksheet_SelectionChange event.

Skip,

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

 

[Predator98]

It is in the Worksheet_Change event. But, it still populates the date if I enter anywhere within the row.

Thanks

 

[SkipVought]

If the code fires when you change your SELECTION, and not when you change your VALUE, then you have code that is in either the WORKSHEET_SelectionChange event or the WORKBOOK_SheetSelectionChange event.

Skip,

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

 

[Predator98]

I received some example code from cckens that I modified to fit my spreadsheet that is working good. Thanks for your input.

If you don't think you can do somethin' it's purty near a cinch you ain't gonna!

 

[SkipVought]

Glad you got an answer. It will help the many Tek-Tips browsers if you would post the code that you finally got to work properly.

It would also be appropriate to...
[blue]
Thank cckens
for this valuable post!
[/blue]
with a little purple star.


Skip,

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

 

[Predator98]

Thanks, Skip...and a little purple star has been sent. Following is the code provided by cckens:

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.

cckens wrote: "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."

If you don't think you can do somethin' it's purty near a cinch you ain't gonna!

 

[SkipVought]

So you don't want to share your code, eh?

Skip,

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

 

[Predator98]

Sorry...pasted too fast...following is the code provided cckens.

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


If you don't think you can do somethin' it's purty near a cinch you ain't gonna!

 

[SkipVought]

Curious. cckens' code exactly met your specifi needs?

I find that extraordinarily fascinating!

Skip,

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

 

[Predator98]

No, not "exactly". As I stated in a post (see above) I modified the code to fit my spreadsheet. Here is my modified version of the code provided by cckens:

If Not Intersect(Target, Range("a1:bx10000")) Is Nothing Then
Dim curr As String 'dim current value of cell
Dim newnm As String 'dim value of date
Dim actcell As String 'dim initial cell address
newnm = Date 'set value to date
Worksheets("Group Tracking Report").Activate
actcell = Target.Address 'set value to inital address
Worksheets("Group Tracking Report").Range(actcell).Activate
ActiveCell.Offset(0, 1).Activate
Range("BX" & Target.Row).Value = newnm
End If

If you don't think you can do somethin' it's purty near a cinch you ain't gonna!

 

[SkipVought]

The highlighted code does nothing except make a selection in another sheet, which is NOT a particularly good coding practice.

    Dim curr As String          'dim current value of cell
    Dim newnm As String         'dim value of date
    Dim actcell As String       'dim initial cell address
    If Not Intersect(Target, Range("a1:bx10000")) Is Nothing Then
       newnm = Date 'set value to date
       actcell = Target.Address    'set value to inital address[b]
       Worksheets("Group Tracking Report").Activate
       Worksheets("Group Tracking Report").Range(actcell).Activate
       ActiveCell.Offset(0, 1).Activate[/b]
       Range("BX" & Target.Row).Value = newnm
    End If

However, if you need to write to this sheet, then this might wotk for you instead...

Private Sub Worksheet_Change(ByVal Target As Range)
     If Not Intersect(Target, Range("a1:bx10000")) Is Nothing Then
        Worksheets("Group Tracking Report").Cells(Target.Row, "BX").Value = Date
     End If
End Sub

Skip,

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

 

[Predator98]

Many thanks. That worked great.

I am very much the "newbie", "rookie", etc when it comes to VB coding. I'm a mainframer by trade but also use Excel extensively in day-to-day resposibilities...but, only Excel & built in functions. I was assigned a task of tightening down a Business Unit's spreadsheet to keep the user's from "mucking" it up. Some of the requested modifications could only be accomplished using VB code (which I've found here and there, modified and made work).

I greatly appreciate your patience and knowledge.

FYI...I will soon be posting another request for, in my "newbie/rookie" opinion, something a little more difficult.

Thanks again.

If you don't think you can do somethin' it's purty near a cinch you ain't gonna!

 

[cckens]

Skip, the VB reference in MS is notoriously unhelpful for "good" coding practices, but in this case it was the only way I could get Excel to do what I wanted. The reference examples (in MS VBA in Excel) listed this as the way to do it, and I followed. It helped that this was a single sheet workbook and I understand that activating a worksheet is unnecssary, but I couldn't find any other way to shorten the code using the provided reference. It DID work, and that is what mattered at crunch time...
I suppose I could have inquired here, but I had a two hour window to get this operational and I spent most of it trying to figure out the procedure. I ought to spend more time in this forum. I could probably pick up a few new tricks that would clean up some of my coding.
Thanks for the star Predator98 and I'm glad it helped.


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)

 

[SkipVought]

We all learn and share.

faq707-4105.

Skip,

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

 

[Aspesim]

Hi Guys

To say I'm newbie at this is an understatement.

I need to know how to add the date (say in coloum L) on which the row (A - K) had been last modified.

I've read the above but don't understand it.

First of all where would you enter the code.

1 step at a time I guess so please be patient with me as I'll be back time and again until its complete

Kindest Regards
Aspesim

 

[SkipVought]

Hi,

Please post your question in a NEW thread.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}