New line symbol appearing after vba form text update of cell 1

[daveh]

Hi,

I have a spreadsheet with a column for text comments. So that users can use update the comments as if the cell were a text box, with new and blank lines, I have got a button at the top of the column which brings up a vb form with a multiline text box on with the current contents of the activecell. They enter any changes to comments and click on the UPDATE button which does the following...

newcomment = CommentsText.Text
ActiveCell.FormulaR1C1 = newcomment
Comments.Hide

This works fine... except that when they've pressed enter in the text box, and the click update, the cell displays a square symbol at the end of every line they've pressed enter after. I've tried automatically removing the symbol by doing the following...

1. Removing any occurances of vbcrlf in the string before it is put back into the cell... but this just removed any new lines and therefore the text appears without the entered line breaks!

2. Doing a find/replace all on the symbols... but the symbol is impossible to copy/paste so i can't isolate it. I think this is because it is not a proper symbol, merely a display symbol.

Is it possible to get the symbol removed from the text when it is in the cell, without it removing the line breaks? Does this symbol have some sort of code that could be used to do a replace with in vb as opposed to using the menu?

Regards,
David.

 

[EBGreen]

Maybe try this:

newcomment = CommentsText.Text
ActiveCell.FormulaR1C1 = Replace(newcomment, vbCrLf, "")
Comments.Hide

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[daveh]

Hi,

Thanks for your help, but unfortunately because that code removes the vbCrLf as it is putting it into the cell, the cell sees one long line of text with no line breaks, and therefore this defeats the object of the form/text box. I suspect the only way to get it to do it is to somehow run a excel function on it, rather than a vb one (just call the excel function from vb). But not sure how to proceed.

Regards,
David.

 

[EBGreen]

In that case, perhaps try:

newcomment = CommentsText.Text
ActiveCell.FormulaR1C1 = Replace(newcomment, vbCrLf, Chr(10))
Comments.Hide

Or:

newcomment = CommentsText.Text
ActiveCell.FormulaR1C1 = Replace(newcomment, vbCrLf, Chr(13))
Comments.Hide

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[daveh]

That worked! Thanks for your help!