Text shape workaround with spaces

[mahmed1]

Hi All

I have a user form that allows users to enter some text in there...I have allowed the agents to press the enter key to go a new line for readability....
Once they have entered the text. The press save and it gets stored into a cell with no extra spacing and the way i want it to look.

I have a shape in my excel linked to this cell the text is in. The problem i am having is that Excel puts a massive space in between each line and i can only fit in 5 lines before it all gets hidden ( i dont want to resize my shape)

If i enter the text without pressing enter and it wraps around a few lines. The correct spacing gets applied to the shape text.

So what im trying to do is allow the user to press enter in the userform text box but when it gets linked to the shape text - remove that extra space so as if i pressed no enter key but excel still shows a new line but without extra space

I hope im making sense

 

[Yongle]

Try this method to remove the offending characters

Dim t As String
t = TextBox1.Text
MsgBox t
t = Replace(Replace(TextBox1.Text, Chr(10), ""), Chr(13), "")
MsgBox t

 

[mahmed1]

Brill - thank you

 

[mahmed1]

That didn’t work 100% for some reason but if if removed all char(10) with say an * and then replaced the * with char(10)
That worked

dont know why

 

[Yongle]

That is rather strange

 

[mahmed1]

Hopefully someone can explain this weird behaviour