Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
|
|||
|
|||
Quote:
Any suggestions? Thank you , Art |
#4
|
|||
|
|||
Quote:
Hope it helps |
#5
|
|||
|
|||
Quote:
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 |
#6
|
|||
|
|||
Quote:
|
#7
|
|||
|
|||
Quote:
Thank you for looking into this, Art |
#8
|
|||
|
|||
Quote:
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 |
#9
|
|||
|
|||
Quote:
|
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
Quote:
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? |
#12
|
|||
|
|||
Quote:
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. |
#13
|
|||
|
|||
Quote:
This does evrything I was only hoping for. Appreciate your expertise and attention to my requests. Art |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date stamp user | Excel Discussion (Misc queries) | |||
Date stamp when a value is entered in a cell | Excel Worksheet Functions | |||
Date stamp when a value is entered in a cell on another worksheet | Excel Worksheet Functions | |||
Create a button that will date stamp todays date in a cell | Excel Discussion (Misc queries) | |||
Setting up a date and time stamp in a cell | Excel Programming |