Date Stamp on update Sheet

[Charmed7]

Can someone help with a Date Stamp in excel.

I just need something to show "Updated Last" time for any time a cell is updated. I have to print this for my boss every once in awhile and would like that date to show. So if I did an update yesterday, but I printed it today, I need yesterday's date to show.

Thanks in advance for your help.

Charmed

 

[KeithG]

You can use the worksheet Select_Change event. Open the attached file for an example. It will add a comment each time a cell is changed.

 

[Charmed7]

I checked that out. I need it to show in a cell vs a comment. I have to print the spreadsheet for my boss and would like him to know when the last it was updated. He hasn't quite grasped the concept of opening a spreadsheet yet.



Thanks for the idea. I tried to play around with it so it wasn't making a comment but I'm just not familiar with VBA.

Any other suggestions would be awesome.

Charmed

 

[KeithG]

So you want the date stamp in the column next to the cell update? I am confused which cell you want the date stamp in.

 

[Charmed7]

Can you tell I moved on to other projects? They are keeping me busy. Which is good I guess. Sorry I didn't get back to you.

What I want to do is put a date stamp in a cell. (Not as a comment) so when any part of the spread sheet is changed, the date will update to Now(). That way if he opens the spread sheet to print it, the last time I changed it will show the date.

Thanks a million for your help.

Charmed

 

[unmarkedhelicopter]

He wants to know WHICH CELL ? or should he just pick one at random ?

 

[Charmed7]

No, he wants to know when any cell in the sheet has been changed/updated. I can't pinpoint a particular cell that will always change, it can be anything.

Charmed

 

[unmarkedhelicopter]

No, he wants to know when any cell in the sheet has been changed/updated. I can't pinpoint a particular cell that will always change, it can be anything.
Charmed

Do you always refer to yourself in the third person ?
KeithG wants to know where do you want the 'date stamp' to go ? or shoudl he just pick a cell at random ? HE knows how to access your sheet change event and when it gets fired for ANY change then he can write a new stamp to YOUR CELL, but which one ? or do you want it, as he puts it "the date stamp in the column next to the cell update" ???

 

[KeithG]

Unmarkedhelicoptor is correct, I need to know where you want the date stamp.

 

[steinfatt1]

Time Stamp on Row Update in Excel

Good Afternoon Everyone,

I have been searching for a solution to what I believe is fairly simple (for anyone that knows what they are doing ... which is not me ). This discussion is very close to what I was looking for.

I have a workbook with multiple sheets. Each sheet is made of rows that are logs of open issues for a project. My boss has requested a column for "Last Updated". So that whenever any cell in a given row changes, the cell in column Z of that row is updated with the current time using =NOW() or something similar.

I figured out how to do it using Worksheet_Change however only for a single cell.. not ANY cell in ANY row and posting the date in the column Z of that specific row.

So if any VBA wizard could assist me I would be much obliged.

Thanks in advance for your wisdom!

-Drew

Ps. Sorry if I am hijacking this discussion, it seemed that the original question has fizzled.

 

[Vonne]

Unmarkedhelicoptor is correct, I need to know where you want the date stamp.

I apologize but, I am new to this. I am looking for a similar formula that shows a date stamp. I have a large spreadsheet that has a "Date Status" column for each roll. I would like to format that column to show the date that any information was changed in a given cell for a specific row. Please note, the "Date Status" column is in the middle of the spreadsheet.

 

[Brianwarnock]

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Cells(Target.Row, 6) = Now
    
End Sub

The above example will put a date and time stamp in col f of the row of the cell that is changed. If you only want a date change Now to Date.

Hope this helps

Brian

 

[Vonne]

Hi Brian, Thank you for the quick response but, I must be doing something wrong. If I want column AS to show the date for any changes made in a give row such as 6 for cells A through BC, how this the formula look? I know just enough about Excel to get myself into a mess. Thanks again for all your help.

 

[Brianwarnock]

The problem here I think is that if you are looking for changes in an area where you write the result , that also is a change and I wonder, not having done it, if you will loop.

To enter the change in a row in AS just change the colindex in Cells to 45

To look for changes in a given row and set of cols then

If Target.row =6 and target.column>=1 and target.column<= 55 then

Cells(6, 45)=Date
end if

That is untested air code but that's the idea.

Brian

 

[scott-atkinson]

Hi all,

I think this is the type of code you are looking for;

Option Explicit
Public preValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
    Const WS_RANGE As String = "w2:w6000" '<== change to suit this is the column that you want the code to monitor for changes.
     
    On Error GoTo ws_exit
    Application.EnableEvents = False
     
    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

' I use this for a weekly monitor sheet so I know on which day data was updated.
            .Offset(0, 2).Value = Date ' <== Once a change has been made it will move the focus from the active cell by 2 columns and insert the current date. Change this to suit
            .Offset(0, 2).NumberFormat = "dd mmm yyyy" '<== This formats the number date into a gregarion formated date. Change this to suit.
        End With
        End If

     
ws_exit:
    Application.EnableEvents = True
End Sub

Enter the code into the worksheet VBA section that relates to the sheet that you want to monitor, if you want to monitor more than one sheet just enter the code in the relevant VBA sections.

Hope this helps.

 

[Vonne]

Brian, I apologize for not following up and thanking you for your help. Once I found someone in the office who had a better grasp of Office 2007, I was able to make the ajdustments you recommended and it appears to be working well.

Scott, I thank you also for your recommendations and will keep them on file as the spreadsheet develops.

 

[Brianwarnock]

Thanks for the response , I'm pleased that you have it working.

Brian