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

[TC1980]

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

[xladept]

Are you clearing the new comment after each run - can we see the whole routine???

[TC1980]

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

[JosephP]

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

[TC1980]

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

[TC1980]

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!