Timestamp Entered Into Comment Field Whenever Corresponding Cell Changes

By comment field I mean the yellow box that is accessed by right clicking on a cell.

TV

Jacc,

This works great! Thank you, thank you, thank you. I edited my original post with an additional request to put the cell value entered next to the date in the comment box.

Did I say thank you already?

TV

Hi Solus,

I pasted the code in a new workbook on sheet1 and it is not working. Did I miss something I had to do?

Thanks,

TV

Originally Posted by Solus Rankin

my code is set up to work on the entire workbook. It needs to go in the workbook module not a sheet module.

It also allows you to "edit" multiple ranges without causing an error.

Ah yes, of course, my newbie qualities are quite evident. There it is in the first line of code calling out Workbook.

Thanks so much, you guys are awesome!

How can the cell data being entered also show up next to the date in the comment window?

Thanks again,

TV

Solus, that works really well. I found that the line:

Formula:

Please Login or Register  to view this content.

allows the bypass of the data getting entered into the comment box. If more than one cell is selected and an entry is filled in, one cell receives the entry but does not record the change. Removal of the above line fixes that.

Thanks again,

TV

Originally Posted by Solus Rankin

You can remove it but what happens when you select more than one cell and delete contents?

Solus,

Thanks for asking me that question because it does misbehave with an error message 'runtime error 13, type mismatch'.

I am able to delete more than one cell but the date is not recorded in any of the comment boxes.

Is there a way to handle multiple cell deletion?

Thank you,

TV

Originally Posted by Solus Rankin

If cells are deleted would you like them time stamped? or would you like all comments deleted?

Definitely time stamped. Right now deleting a cell places a blank after the date in the comments and that works out just fine.

Thank you Solus.

TV

Solus,

That's a nice and simple solution. There's just one little snag, if many cells are selected and delete is pressed, the data is erased and then the new popup you provided shows up.

Thanks for all your help on this and sorry for the back and forth.

Cheers,

TV

Originally Posted by HaHoBe

Hi, tv69,

should not be a problem.

Please Login or Register  to view this content.

Ciao,
Holger

Holger,

Thanks for jumping in, you guys are great. You seem to whip up this code like it's your native language. I hope I can get halfway there someday.

That works really well but I discovered another way to cheat. If you copy any empty cell and paste into a cell with data and with dates logged in the comment box, the dates in the comment box are cleared and the current time stamp of the pasted cell is entered. I suppose disabling copy and paste from an empty cell to a cell with data would prevent this.

Thanks so much.

TV

I have spent some time with this solution and I feel it may need some tweaking. I am trying to do a couple of things that limit the work in the spreadsheet and also cause some things to break.

First, the solution provided is for an entire workbook. I would like to have this comment timestamp work for one sheet only.

Second, the comment timestamp works on all fields. Can I instead call out a few columns to have the timestamp applied to.

Third, I need to insert rows of data via a macro linked to a button. With the code preventing more than one cell being edited, I am prevented also from inserting a new row of data.

Thank you for your help.

TV

Hi Solus,

It will probably be a few columns with data from ranges like A2:A1000, E2:E1000, H2:H1000. I will have a header row and that's my reason for starting at row 2 for each. As I develop the spreadsheet I may need to add more columns.

Thank you for your help Solus. I really appreciate it.

TV

Solus,

You're awesome! That works great and is so flexible with the column call outs.

I just need to be able to insert a row of blank data without the error popping up "Please edit one cell at a time, action reverted". I have a macro linked to a button. Clicking the button inserts a new blank row in row 2 (below the header), while incrementing the next order number in cell A2.

Thanks so much Solus.

TV

Hi Solus, that worked great.

I found another way to bypass and erase the recorded dates in the comment fields. You simply copy a blank cell and paste over a cell with values and the comments in that cell reset and the current time and value are recorded. I have already disabled right click functions.

I'm also just wondering if you could suggest a good resource to start learning Excel VBA? How long have you been at it?

Thanks very much Solus.

TV

Thanks for the link Solus. I'm diving into much of this and find it's a good way to learn but the syntax always kills me. In addition it's hard to know what function does what without a good resource. Thanks to the internet and forums like this that are so helpful to people starting out.

In my previous post I was not clear enough and I still have a problem where use of CTRL-C and CTRL-V used to copy/paste into a cell with data results in clearing any previous timestamps. How would you go about preserving existing timestamps in a target copy/paste cell?

Thank you kindly.

TV

Hi Solus,

Thank you. The new code works but it locks all cells from having data entered, even empty cells.

TV

Hi Solus, here is the code. Thanks again.

TV

Please Login or Register  to view this content.

Hi Solus,

The 'Submit' Sub is meant to transfer an order from the 'Incoming Purchase Order' Workbook to a 'Submit Temp' Workbook. Workbook 'Submit Temp' is really a staging area. Someone from production will then pull orders from the 'Submit Temp' into a 'Production' workbook.

The 'Incoming Purchase Order' Workbook has a single user 'A' with unique password. The 'Production' Workbook has a single user 'B' with a unique password. The 'Submit Temp' Workbook is meant to work around the passwords and to give Production Control when to pull the info.

Thanks,

TV

Orders submitted will be controlled by user 'A' accessing the 'Incoming Purchase Order'. In my 'Submit' code a time stamp is added in column 13 of a row when submitted. The code also checks if a value exists in column 13, so a row can only be submitted once. The document will be protected and column 13 will be locked. In addition the corresponding comment box will have the time stamp and cell value recorded (as is being done with the initial code you wrote for me).

There are some funky things happening when combining a protected sheet with the limit of editing one cell at a time in the code you provided. I'm trying to debug and having a bit of trouble. Mind you, all this adds to the VBA programming experience so I don't mind. I don't want to confuse the matter by mentioning these specifics of the funky occurrences right now.

Thanks Solus,

TV

Hi Solus,

What you provided works extremely well with the exception of no time stamp recorded in column 13.

I added the following code:

Please Login or Register  to view this content.

following this line in your code:

Please Login or Register  to view this content.

and the date stamp works fine.

I'm curious to learn from Holger or yourself Solus, why it is not reliable to work with either Selection or ActiveCell?

Right now the Sub for adding time stamps in comment boxes is disabled. I will enable it and see if it functions as intended.

In retrospect it was probably more painful to just start with the comment box time stamp and not disclose everything I wanted the workbook to do. As I discovered limitations in the procedure allowing time stamps to be circumvented it did not go very smoothly to try and fix each case while breaking other functions.

Up until a couple of weeks ago I had always used excel in the most basic of ways and not clearly understood what effects things like protecting a worksheet really have. I'm learning a lot from my mistakes and from your guidance Solus and Holger. My apologies if this is going in a roundabout way and thank you very much for your help and support.

Cheers,

TV

Hi Solus,

Let me give a quick recap of what I'm trying to achieve here in the hope it makes it clear as to the entire scope.

1. Click macro button to add a new order row on sheet1 row 2, this is done by copying from a 'template' sheet in the same workbook where cells are pre-formatted. Pre-formatting includes cells with pull down menus, formulas linked to pull down values, and blank cells formatted for date, and order number. A counter increments to generate the next order number and that is also placed in cell 'A2'. The sheet is protected with a password and some of the columns of data are locked.

This part works really well right now and here is the code I'm using.

Please Login or Register  to view this content.

2. On top of this I would like to add the time stamps to specific columns of data and this where your code comes in. I'm currently commenting it out because it's causing errors when the new order macro above is run.

Please Login or Register  to view this content.

The problem is it will not let me add a new row when the Sub New_Order() is being run. It returns a Run-time error '1004' Insert method of Range class failed and fails at this line in the 'Sub New_Order()' code:

Please Login or Register  to view this content.

When I was able to bypass this error I then ended up with another error caused by this part of the code:

Please Login or Register  to view this content.

3. Well if that is not enough, then this code that was introduced to prevent copying and pasting on top of cells with time stamps, effectively losing the history of time stamps, causes another error with the 'Sub New_Order()' code.

Please Login or Register  to view this content.

It causes formatting to be messed up and formulae to disappear when the insertion of the new row takes place.

My apologies for the long post but I should have provided more context in the first post of this thread.

Thanks once again Solus.

TV

I'm learning so much!

Thank a whole bunch Solus.

TV

Solus,

Yes the sheets are protected. I scoured the net to figure out how to pull it off and it seems to be working well so far. Perhaps you are right though and the comment box time stamps won't work as desired with my protection settings.

I don't have access to the spreadsheet right now but I can send it to you in the morning.

When sheet protection is set with password, the only ticked boxes are:
select unlocked cells
format cells
format rows
sort
use auto filter

I have two sheets in my workbook. All data is entered on 'Sheet1'. I also have a 'Template' that has the same layout as 'Sheet1'. The 'Template' sheet only includes the heading row and one blank row in row 2. This row 2 although appearing blank has all the goodies that includes drop down lists, formulae activated by drop down list selections, blank cells for other data entry such as dates, and all formatting for cells including justifications, word wraps, categories, and whether cells are locked and hidden.

In this row 2 Any cells with formulae are locked and hidden. With sheet protection settings set the way specified, these cells cannot even be selected.

Also in this row 2 cells with drop down lists or cells that required dates and other data to be filled in are not locked and not hidden. These cells are fully accessible when sheet protection is activated. The time stamp in the comment box will record changes to these cells. In my tests when things were not breaking, the cells with formulae do not record time stamps since the values are calculated. It is not necessary but out of curiosity I wonder if it is possible to record values calculated by formulae as an event that can be tracked.

Activating sheet protection allows access to cells as outlined but sorting was still non existent. Protection settings say that sorting is allowed yet impossible. What's going on?

Three things have to come together to give the full sorting capabilities with a protected sheet. The trick is to not allow 'Select locked cells' in the sheet protection, combined with "Allow Users to Edit Ranges" in the 'Review/Changes' of the ribbon. You can set an editable range (I selected the entire sheet) and even apply a password for access to this editable range (I did not set a password). You could also assign permissions to groups or users to access this editable range. Next you must activate the "Filter" tool found in the 'Data/Sort & Filter' part of the ribbon which places little drop down selections on the right side of each column heading for filtering options. The combination of setting a range to "Allow Users To Edit Ranges" along with the "Filter" activated means that you can now also use the filter function to sort from largest to smallest or smallest to largest with sheet protection enabled. Sorting from largest to smallest or smallest to largest is not available without setting an editable range. There may be other features not otherwise available but I have not delved into them. Protecting the sheet with the above mentioned settings while ensuring not to tick the "Select locked cells" setting (this setting is at the top of the list of options after pressing the 'Protect Sheet' button) and making sure to tick the "Sort" and "Use AutoFilter" settings below, will give you the most flexibility for sorting.

In short, the three steps necessary to have full sorting functionality on a protected sheet are:
1. under 'Review/Changes/Allow Users to Edit Ranges' set editable range (with a secondary password if desired or with group/user rights)
2. under 'Data/Sort & Filter' activate the 'Filter' tool to allow for filtering options via drop down selections in each column heading
3. under 'Review/Changes/Protect Sheet' do not allow selection of locked cells in the sheet protection dialog box (do not tick box "Select locked cells") and tick "Sort" and "Use AutoFilter"

With the settings I have specified, it is impossible to insert a new row. Anytime a sheet is protected and a row or column has even one locked cell in it, it is impossible to insert or delete even if those functions are enabled in the sheet protection settings. In fact it is impossible to even select a row/column under these circumstances if a single locked cell is present. Only if the entire row/column has non locked cells are they selectable with the ability to Insert/Delete a Row/Column.

Despite this, my workaround is to turn off sheet protection, make the change, and reapply the sheet protection all via VBA. I also did this in a couple of areas of the comment box time stamp to make it work. Is there any harm in doing so? It's a workaround that seems seamless right now but maybe it can be breached between the interval when protection is off and then back on?

As mentioned in my previous post, with your current code for the comment box time stamp, a new row or New Order can not be inserted in the worksheet. It's a double whammy too because I believe the portion that prevents more than one cell from being edited for the comment box time stamp also prevents a new row from being inserted.

I hope what I have described above about sheet protection might be helpful to you and others. It all came together from many, many searches over the past couple of weeks with the "Allow Users to Edit Ranges" being the last piece in the puzzle, allowing full sorting functionality via filters.

Regards,

TV

Originally Posted by Solus Rankin

The sheets are protected? How is a user supposed to edit them anyway?

The won't be able to copy and paste on a protected sheet, nor will they be able to change cells. Therefore no comments will be added.

Is there anyway you can attach your workbook as an example? From the code I think I can discern the direction you'd like to go, but it would be nice to have all the details.

To attach your workbook, click the 'Go Advanced' button below and use the paperclip in the toolbar to navigate to your workbook.

Hi Solus,

I'm open to any suggestions you may have.

I have attached the document. All passwords have been set to "solus".

Cheers,

TV