Userform textbox to combine all cells comments

[mariec_06]

I have the following code which shows in the text box "notes" (in a userform) the cell comment if any

This works fine however I would like this text box to display all the comments for each cell one after the other after a word wrap.

here is my code working for the first cell found with comments but it overwrite the previous comments with the last found

Please Login or Register  to view this content.

Basically i would like to add after CHR(10) the next cell comments something like

Please Login or Register  to view this content.

can someone tell me if feasible and how to do it?

[pjwhitfield]

In the loop add the new data to itself rather than making it equal to

Please Login or Register  to view this content.

So this will take whatever is in Me.Notes.Value and add the new data on the end of it.

[buran]

Note that you should use On Error Resume Next with great care. Remember it may mask an error that you actually want to catch in order to debug/correct your code.

[mariec_06]

Thank you Pjwitfield it was so simple, however it does not appear correctly as I want.

Here a print screen of the notes box

[ATTACH]483475[/ATTACH


I would like it to appear

QTE
INSP LP: USD 1,225.00 - INSP LP
OTHERS: USD 67.25 - wrong overage STASCO calc
Unqte

Buran, thank you for your advice however if I delete all the reference to on error, I got an error message "Object variable or with block variable not set"

here the code without any reference to "on error"

Please Login or Register  to view this content.

[buran]

Buran, thank you for your advice however if I delete all the reference to on error, I got an error message "Object variable or with block variable not set"

In your code you you use On Error Resume Next two times. The first time when you set the range. Here you don't have any reason to do so. The second case is when you try to loop over cells with comments. Here again you don't have reason to use it. You get the error because there are cells that don't have comments and you don't want to mask the error (you may have other errors - e.g. incorrect index when refer to cells rows and columns), but handle it right. Actually you try to do so using special cells, but you are not doing it right. - you select the cells with comments, but after that you again loop over all cells in rngComments, not only the selection. Another way would be to check that the cell. Comment Property is not nothing.
Here is your code little modified, but I'm not able to test is as I don't have workbook.

Please Login or Register  to view this content.

[mariec_06]

Thanks Buran

I managed to do it too as follows but yours works fine and is simpler

Please Login or Register  to view this content.

However concerning the appearance I have still the same issue, the code does not insert the additional after notes.value on another line although wrapword and multilines property are set as true......

[buran]

can you upload a workbook. I'm not sure how your comments look like and what you want to get in the text box

[mariec_06]

Sorry my mistake, the comments were entered without word wrap. All solved now

Many thank to both of you