Text box to Merge Cell in excel

hi all,

I have created a Text box and set the multi line to true, this allow the user to key in some information into it. I have also merge some of the cell in Excel so i could transfer those text from the text box into the merge cell.

The problem is when those information is pass into the merge cell, i have those box like character (VbCrLf) appearing in the cell if i type multi-line.

Anyone know how can i get rid of those square character ?

Im not sure if this will work but i had the same problem with an old spread sheet program i copied the text in the text box into a string variable and went through each character using the mid() function checking for the vbcrlf and replaced it with
CHR(10)+CHR(0) this worked but im not sure if it works with excel.

hi and thanks for the help.

well, I came out with another way which works but indirectly.

What i did I splitted the cell in excel and i split the string in text box. Using the Spiltted result in the multiline Text box, the result was transport into the excel file, it work fine this way. I got these code from some website. Forums are great !

For those interested:
example

'Slice up the String
SplitResults = Split(Text1.Text, vbCr & vbLf, True, vbTextCompare)

'Loop through the results
For ThisSlice = LBound(SplitResults) To UBound(SplitResults)
oExcelApp2.Range("address" & (ThisSlice + 1)).Value = SplitResults(ThisSlice)
Next ThisSlice