Prob. with TextBox in Excel 3

[LucieLastic]

I have a text box on a user form in which it is anticipated several lines of text will be needed. I have set the properties of the textbox so that enter results in carriage return, word wrap is on and multiple lines is true. Scroll bars appear as if by magic and it is happy days.

However, when i write it to the XL cell, it shows the little square boxed denoting a carriage return. It also prints them.

How do I get rid of them ?

I know the answer is probably simple, but I am a newbie to vb !

Thanks !

 

[Zathras]

Hey weez! Fancy meeting you here.

The little square that appears is the carriage return character (#13). You have a couple of ways to go. You could strip out the entire CRLF sequence (#13#10 or vbNewLine in VBA-speak) and set the alignment for the cell to word wrap, or you could leave the LF part in and control the line breaks that way. (The little square will not appear if there is just LF and not CRLF.)

One way to do the latter is to use code similar to this for the click event:
[blue]

Option Explicit

Private Sub CommandButton1_Click()
  [A1] = StripCR(TextBox1.Text)
End Sub

Function StripCR(AString As String) As String

[green]

' Removes carriage return characters (#13)
' but leaves new line characters (#10)

[/color]

Dim x As Integer
  StripCR = AString
  x = InStr(AString, vbNewLine)
  While x > 0
    StripCR = Left(StripCR, x - 1) + Mid(StripCR, x + 1, 999)
    x = InStr(StripCR, vbNewLine)
  Wend
End Function

[/color]
If you wish, you could put the StripCR() function in a separate code module to allow it to be invoked from places other than just your form.

 

[LucieLastic]

hi Zathras, the real weez here!

My other (better) half has used my handle to post this message, I'm trying to avoid it (VB that is)!

Anyway, big thanks for reply. Is there not an equivalent of Delphi's RichEdit box in VB?

lou

 

[Zathras]

Not on my machine, but if you right-click on a blank space on the tool pallette (not on the tab), you should see an option for Additional Controls... -- take a look.

 

[LucieLastic]

hi again

Learning as I'm going along here. Is there not a simple/standard way of having a multiline edit box in VB? What do components/methods do VB programmers use when they write an interface that needs a multiline/paragraph of text to be input?

thanks for help
lou

 

[Zathras]

I think you found it: multi-line text box. (Although I haven't used it much myself.)

If I don't respond any further for a few days, don't take it personally. I'm going out of town and won't have internet access until I get back sometime in the middle of next week.

 

[Bowers74]

You can continue using the standard Multi-Line Text Box. The "mistake" that a lot of people make when creating Multi-Line strings is to use one of three "characters" to insert a line (Carriage Return):

1. vbCrLf
2. vbNewLine
3. Chr(13) or Chr(10)

These are also the same "characters" that VBA also uses in the Multi-Line TextBox when you press Enter. The problem with this (as you have already stated) is that you get those stupid, ugly little squares in your string displayed as well.

Zathras,

Your function is great, but there is another (easier) way to do this:

The "character" vbLf does not give you those squares. It is basically the same as adding a line in a cell by pressing Alt+Enter. So the following line of code will replace the vbCrLf "characters" with vbLf characters, and you will get the text returned to the cell without the squares:

For example, use this to get the text you want from TextBox1 into cell A1 on the ActiveSheet:

[A1] = WorksheetFunction.Substitute(TextBox1, vbCrLf, vbLf)

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[TonyJollans]

Hi weez,

The Textbox contents include Carriage Return / Line Feed sequences to separate lines. When you copy this to a cell in code (as per Zathras' example) you get, as you observe, the little squares displayed to represent the Carriage Returns.

If, on the other hand, you let Excel copy it for you it is smart enough to know what you want and it automatically strips out the Carriage Returns. To do this just set the Control Source of the Textbox to your cell; no code, no little boxes.

Enjoy,
Tony

 

[LucieLastic]

hi All

Many thanks for all your help, we're really grateful.

best regards
lou (and better half)