+ Reply to Thread
Results 1 to 2 of 2

Detecting deleted comment/protecting comments

  1. #1
    Registered User
    Join Date
    03-20-2009
    Location
    Lithuania
    MS-Off Ver
    Excel 2013
    Posts
    99

    Detecting deleted comment/protecting comments

    Hi,

    Is it possible to somehow detect that comment was deleted in a cell?
    I'm currently using comments to put previous value of a cell, so that there would be history of what was before the change in the cell. To avoid users accidently deleting comments I'm using sheet protection (I don't allow editing objects, which protects comments too). So whenever user changes something in the cell the following actions in worksheet occur:
    1. worksheet unprotects itself;
    2. worksheet enters previous value into a comment;
    3. worksheet protects itself.

    However, users expressed the need to have shared Excel file. But whenever I enable sharing of the file, protecting/unprotecting of Excel file crashes - it's not possible to use protect/unprotect feature in shared Excel file.
    So I'm wondering, if I avoid using protect/unprotect feature, is it possible to somehow detect that comment was deleted in a cell? Or maybe there's some other way to protect comments in shared excel file?

    Regards,

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Detecting deleted comment/protecting comments

    You could use VBA to:
    - create a list of comments in (hidden) sheet "CommentMaster" - with 2 columns (1 = SheetName&CellRef 2 = Comment text)
    - periodically create list of all comments in (hidden) sheet CommentLatest" (same 2 columns)
    - match entries in the 2 sheets (using Match function)
    - report differences

    Then manually fix the comments that require fixing
    and use VBA to:
    - create a new "CommentMaster"
    - delete "CommentLatest"
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 12-04-2013, 02:23 AM
  2. [SOLVED] Comments from drop down list - delete if cell value deleted.
    By Duguids in forum Excel General
    Replies: 5
    Last Post: 09-11-2013, 08:24 AM
  3. Replies: 1
    Last Post: 04-27-2012, 02:07 PM
  4. Detecting a Comment in a cell
    By Rick_Stanich in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-16-2010, 06:46 PM
  5. Protecting a sheet with comments
    By mehulj99 in forum Excel General
    Replies: 4
    Last Post: 01-17-2010, 07:41 AM
  6. Protecting Comments
    By craig mullen in forum Excel General
    Replies: 2
    Last Post: 05-29-2007, 10:47 AM
  7. protecting sheet blocking comment creation
    By Amadis in forum Excel General
    Replies: 3
    Last Post: 03-09-2006, 07:50 AM
  8. Protecting worksheet-Unable to view comments
    By Frederic in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-05-2005, 10:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1