Date Stamp by User and cell value

[rtour957]

Dear All,
Searching the forum I found the code to date stamp the comment but wha
I need is a bit different and unfortunately more complicated.
Say I have validation drop down menu in Coumn A, Column C and Column
to choose from: Sent, Received, Returned, Processed. There are about
different users who use this spreadsheet and I need a user specific an
action specific date stamp as many time as the values in A, C,and E ar
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,
Ar

 

[Auric__]

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 here:

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.)

 

[rtour957]

rtour957 wrote:
-

I'm not sure what "comment" you mean, but I'm going to assume you wan
some
sort of logging. Have a look at the logging code I posted last year, i
the
thread called "Macro to create log file". The current version of th
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 th
workbook
being tracked. It's in the public domain so feel free to change it a
you
desire... if it does what you need it to, that is. (It track
*everything*,
not just 3 columns.)

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

Thank you , Ar

 

[rtour957]

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

Hope it helps

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

Respectfully,
Ar

+-------------------------------------------------------------------
|Filename: Comment_datestamp.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=783
+-------------------------------------------------------------------

 

[Gizzmo]

Thank you,
It does almost exactly what I need but does not keep the log of previou
changes/date stamps. The values in the cell may change several times. S
everery time it changes I need a date stamp that would stay and an
future date stamps would be recorded on top of the previous ones - t
keep a log.
Also preferebly a specific action/cell value that cause the date stam
be recodred as well.
I attached small file and manually formatted comments in few columns t
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
ask why you specifically want it in the Comments v a seperate log? I a
not an expert but I suspect that the comments box will have a limit t
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

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Gizzmo]

I expect cell value to be changing no more than 4 times so hopefull
character limit won't be a problem. I agree that a separate log genrall
makes makes much more sense but it also means that you'll have to go t
a different tab where you'd keep it. With the total number of those row
and columns where I need date stamp it will be a nightmare to switc
back and forth, whereas having date stamp and user in the comments wil
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 tw
checks one to determine if there is Data Validation, if there is then d
something, in the do something part it then determines if there is an
existing comment. If there is then add to the existing comment
otherwise add comment.

Regards,

Gizzm

+-------------------------------------------------------------------
|Filename: DateStampComments.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=784
+-------------------------------------------------------------------

 

[rtour957]

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 ca
format the box and make it as big as you want. Here, though, even if yo
format it to see all entries - next time you run the script the commen
box shrinks to default size. Is ther a way to make it either expan
automatically with each entry or format from the get go so it'll sta
big enough to accomodate all entries. otherwise it displays about
lines only.
I tried to look up the code to change the ranges to my needs and th
only way I know - View Code in the Worksheet did not show any. How do
see the VBE code in here?

Thanks again for your attention to this,
Ar

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Gizzmo]

Thank you very much! It does exactly what I was looking for.
Two questions: Normally, if you insert a comment into the cell you ca
format the box and make it as big as you want. Here, though, even if yo
format it to see all entries - next time you run the script the commen
box shrinks to default size. Is ther a way to make it either expan
automatically with each entry or format from the get go so it'll sta
big enough to accomodate all entries. otherwise it displays about
lines only.
I tried to look up the code to change the ranges to my needs and th
only way I know - View Code in the Worksheet did not show any. How do
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 hel
thus far.

The reason for the comment size not staying the same is that the cod
actual delets existing comment and then adds ne comment box, in m
previous post I said ...add to exisiting comment... this not a clea
explanation of what the code was doing. I will try and see if there is
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 t
Worsheet Change event, what are you doing

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[rtour957]

Ok Art,

I have added code to resize comment box.

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

PS if you search www.Ozgrid.com and have a look at some of the code tha
contributors have post such as Andy Pope OzMVP(Roobarb) and many other
you will find a way to almost do anything in Excel. I new nothing abou
excel 14 years ago and today I am always learning thanks to people lik
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.

Ar

+-------------------------------------------------------------------
+-------------------------------------------------------------------