+ Reply to Thread
Results 1 to 6 of 6

Problems with cell comments - autosizing, multiline - stops working after 3 to 5 lines

  1. #1
    Forum Contributor
    Join Date
    10-22-2012
    Location
    London, UK
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    162

    Problems with cell comments - autosizing, multiline - stops working after 3 to 5 lines

    Hi,

    Looking for someone much smarter than me (not hard) to see if they can work out why I'm getting some strange behaviour adding comments to cells using VBA.

    Some background: I'm using cell comments within VBA code to create an audit trail when cells are amended. The cells are protected and if a user wants to make a change (e.g. correct a typo) then they have to enter the reason why (into a simple userform) that updates cell comments with their environ username, date, time and reason.

    This is the code I'm using at present - which works well up to a point:

    Please Login or Register  to view this content.
    c is the cell, just in case that's not clear, and ChngRsn is the text the user enters to explain the reason for the change.

    I can run the above code up to about three times depending on the length of the text and it works really well, but over that then it just doesn't update the cell comments. No error message, steps through the code all fine, NewCmt is not empty - but just doesn't add anymore.

    Anyone have any ideas on why this might be - or an alternative I can try to achieve the same end???

    Any assistance appreciated - as always.

    Thanks, TC

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Problems with cell comments - autosizing, multiline - stops working after 3 to 5 lines

    Are you clearing the new comment after each run - can we see the whole routine???
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Forum Contributor
    Join Date
    10-22-2012
    Location
    London, UK
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    162

    Re: Problems with cell comments - autosizing, multiline - stops working after 3 to 5 lines

    Hi,

    Thanks for responding.

    No, I'm not clearing the comment as it's to track all changes - so in order to maintain an audit trail it needs to add to existing comemnts. The reality is that it's unlikely that more than one or two changes might need to be made to a single cell, but this reliance on the 'unlikely' just isn't good enough for me.

    The fact it behaves inconsistently is also troubling me though it does seem to be related somewhat to character length - though researching has failed to show any character limit on cell comments - and I would have expected an error rather than it just not doing anything. Could this be a glitch?

    I can't post the whole sub routine as it's quite lengthy and much of it is not relevant, but hopefully the below will help make more sense of it?



    User select the cell(s) they wish to amend then select 'Amend / Update Case Summary' from an 'Actions' menu

    The AmendCaseSummary subroutine then:
    Checks the selected cell(s) are within an allowed selection - if not warns the user and exits the code
    If the selection is valid then loads a 'Description' userform which asks the user to enter the reason for the change, e.g. Typo (asigned to the ChngRsn variable)

    Unlocks the cell to allow the user to make a change to the contents (but the user cannot edit comments and defeat the purpose of the audit trail)
    Adds reason for the change along with environ username and system date & time to comments / existing comments.

    Message to advise user they can make the changes that they need to.



    The issue is with the code posted above, in that it funs fine for the first three to five times then runs but doesn't actally make any changes to the comments (still unlocks the cell, etc) and doesn't throw out an error message either...

    Very strange.

    I would post a simplified workbook but we're blocked from uploading anything from where I work...

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Problems with cell comments - autosizing, multiline - stops working after 3 to 5 lines

    which version of excel and why are you using notetext rather than addcomment? I believe notetext is limited to 255 characters
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  5. #5
    Forum Contributor
    Join Date
    10-22-2012
    Location
    London, UK
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    162

    Re: Problems with cell comments - autosizing, multiline - stops working after 3 to 5 lines

    2003, hmmm - not thought of that - just used notetext previously so saw no reason to change - will try now...

  6. #6
    Forum Contributor
    Join Date
    10-22-2012
    Location
    London, UK
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    162

    Re: Problems with cell comments - autosizing, multiline - stops working after 3 to 5 lines

    Yep that fixed it!

    New code here:

    Please Login or Register  to view this content.
    Thanks so much for this - will avoid notetext in the future!

+ 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. [SOLVED] Macro stops working after I alter a cell
    By madhatter40 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-22-2013, 04:09 PM
  2. [SOLVED] Sorting on textbox input - Problems when inserting a new row (macro stops working)
    By adrianrff in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-02-2013, 05:03 PM
  3. [SOLVED] TextBox Multiline: have <br> tag between lines
    By monak83 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 04-20-2012, 11:26 AM
  4. SUMIF stops working after 1618 lines?
    By shockeroo in forum Excel General
    Replies: 2
    Last Post: 06-22-2010, 11:50 AM
  5. Autosizing comments
    By hoopz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-06-2006, 08:24 PM

Tags for this Thread

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