CRLF in text box

[Neil Miller]

Can anyone explain the following behaviour, or perhaps more likely,
tell me what I'm doing wrong? I thought I had done this many times
before

Set up a Userform with a textbox, e.g. TextBox1, that has both
EnterKeyBehavior and MultiLine set to True. Enter a single character
followed by a Return - the textbox should then contain, for example:
"aCRLF"

Now, if I want to remove the CRLF, I have been using:

With UserForm
Do While Right(.TextBox1.Text, 2) = vbCrLf
.TextBox1.Text = LeftB(.TextBox1.Text, Len(.TextBox1.Text) - 2)
Loop
End With

Now, this code loops, because when I examine the results of the Left
(or LeftB - the results are the same) function, .TextBox1.Text still
contains the CRLF. The only way I can get it to work is to store the
original text, set .TextBox1.Text to "", and then store the results of
the Left function back in the textbox.

What is happening here? It's an easy bypass, but I may have used this
technique elsewhere and would like to know whether I need to change
other instances of this type of code.

Excel version is 2002 running on XP professional.

Many thanks in advance for any help received.

Neil

 

[Tom Ogilvy]

Your code worked fine for me. (changed userform to userform1)

 

[Neil Miller]

Thanks for your prompt response, but it's still not working for me. I
tried the following workaround:

Dim mY_stR As String
With UserForm1
If Trim(.TextBox1.Text) <> "" Then
mY_stR = Trim(.TextBox1.Text)
.TextBox1.Text = ""
Do While Right(mY_stR, 2) = vbCrLf
mY_stR = Left(mY_stR, Len(mY_stR) - 2)
Loop
.TextBox1.Text = "" = mY_stR
End If
End With

When the contents of mY_stR are stored back into the textbox, then the
VBA watch for that field, and the textbox on the displayed form, both
show "False".

In the test you tried, is MultiLine set to True? I can get round the
problem if this is set to False, but that really defeats the object.

Many thanks.......Neil

 

[Neil Miller]

Sorry Tom - the last post with the workaround contained a typo:

.TextBox1.Text = "" = mY_stR

should have read:

.TextBox1.Text = mY_stR

The workaround does work, of course, but I still have the original
problem described in my first post.

Regards....Neil

 

[Tom Ogilvy]

Yes Neil, MultiLine and EnterKeyBehvior were both as you specified (true).
I put in several enter keys at the end of the a and it worked fine, removing
them all.

I put on two commandbuttons to peform the removal and document what was in
textbox1:

Private Sub CommandButton1_Click()
For i = 1 To Len(TextBox1.Text)
Debug.Print i, Asc(Mid(TextBox1.Text, i, 1))
Next
End Sub

Private Sub CommandButton2_Click()
With UserForm1
Do While Right(.TextBox1.Text, 2) = vbCrLf
.TextBox1.Text = LeftB(.TextBox1.Text, Len(.TextBox1.Text) - 2)
Loop
End With

End Sub

 

[Neil Miller]

Definitely the last post on this - I promise!

Observations:

1) The problem only occurs when there is a single character in the
textbox followed by CRLF;

2) If the same code is run on Excel 2000 running under Win 98, the
problem does NOT occur.

I guess I have to put it down to freak sunspot activity?

Thanks for taking the time to help Tom.

Neil