Date Stamp by User and cell value
 

Dear All,
Searching the forum I found the code to date stamp the comment but what I need is a bit different and unfortunately more complicated.
Say I have validation drop down menu in Coumn A, Column C and Column E to choose from: Sent, Received, Returned, Processed. There are about 6 different users who use this spreadsheet and I need a user specific and action specific date stamp as many time as the values in A, C,and E are changed - to be tracked and displayed preferably in the comment. If not, then a separate table might work. There are about 80 rows of data.

Thanks in advance,
Art

Date Stamp by User and cell value
 

rtour957 wrote:

Searching the forum I found the code to date stamp the comment but what
I need is a bit different and unfortunately more complicated.
Say I have validation drop down menu in Coumn A, Column C and Column E
to choose from: Sent, Received, Returned, Processed. There are about 6
different users who use this spreadsheet and I need a user specific and
action specific date stamp as many time as the values in A, C,and E are
changed - to be tracked and displayed preferably in the comment. If not,
then a separate table might work. There are about 80 rows of data.

I'm not sure what "comment" you mean, but I'm going to assume you want some
sort of logging. Have a look at the logging code I posted last year, in the
thread called "Macro to create log file". The current version of the code can
be downloaded he

http://auric.altervista.org/excel/track_changes.bas

The code in that file needs to go into the ThisWorkbook class of the workbook
being tracked. It's in the public domain so feel free to change it as you
desire... if it does what you need it to, that is. (It tracks *everything*,
not just 3 columns.)

--
Courage of the heart is very rare.

Thanks for the link I checked out. I do not want a log be created in another sheet. I thought about having comment in the cell created that would be date stamped with the user ID and cell value that the user chose from the drop down menu in that cell.
Any suggestions?

Thank you , Art

Refer to the attached, it will put the date and user name as comment in any cell that is changed.

Hope it helps

Thank you,
It does almost exactly what I need but does not keep the log of previous changes/date stamps. The values in the cell may change several times. So everery time it changes I need a date stamp that would stay and any future date stamps would be recorded on top of the previous ones - to keep a log.
Also preferebly a specific action/cell value that cause the date stamp be recodred as well.
I attached small file and manually formatted comments in few columns to make it easy to understand what I am looking for.

Respectfully,
Art

Will have a look at it later as I am at work at the moment. Also can I ask why you specifically want it in the Comments v a seperate log? I am not an expert but I suspect that the comments box will have a limit to the number of characters, so I am worried you will reach this limit. Also adding comments to every cell will increase your file size etc.

I expect cell value to be changing no more than 4 times so hopefully character limit won't be a problem. I agree that a separate log genrally makes makes much more sense but it also means that you'll have to go to a different tab where you'd keep it. With the total number of those rows and columns where I need date stamp it will be a nightmare to switch back and forth, whereas having date stamp and user in the comments will make it possible to dynamically check it without leaving the worksheet.

Thank you for looking into this,
Art

Hi Art,

Have modified, hopefully this is what you require. I have put in two checks one to determine if there is Data Validation, if there is then do something, in the do something part it then determines if there is any existing comment. If there is then add to the existing comments otherwise add comment.

Regards,

Gizzmo

Sorry attached the wrong file try this one!

Thank you very much! It does exactly what I was looking for.
Two questions: Normally, if you insert a comment into the cell you can format the box and make it as big as you want. Here, though, even if you format it to see all entries - next time you run the script the comment box shrinks to default size. Is ther a way to make it either expand automatically with each entry or format from the get go so it'll stay big enough to accomodate all entries. otherwise it displays about 5 lines only.
I tried to look up the code to change the ranges to my needs and the only way I know - View Code in the Worksheet did not show any. How do I see the VBE code in here?

Thanks again for your attention to this,
Art

Hi Art,

Glad to hear it does what you want and that I have been able to help thus far.

The reason for the comment size not staying the same is that the code actual delets existing comment and then adds ne comment box, in my previous post I said ...add to exisiting comment... this not a clear explanation of what the code was doing. I will try and see if there is a way to rectify and get back to you.

Not sure why you can't see code, all I do is use Alt+F11 and then go to Worsheet Change event, what are you doing?

Ok Art,

I have added code to resize comment box.

Also to view code use Alt+F11 select ThisWorbook, Workbook and SheetChange.

PS if you search www.Ozgrid.com and have a look at some of the code that contributors have post such as Andy Pope OzMVP(Roobarb) and many others you will find a way to almost do anything in Excel. I new nothing about excel 14 years ago and today I am always learning thanks to people like you and Andy Pope.

Enjoy.

Thank you so much!
This does evrything I was only hoping for.
Appreciate your expertise and attention to my requests.

Art