Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CRLF in text box
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CRLF in text box
Your code worked fine for me. (changed userform to userform1)
-- Regards, Tom Ogilvy Neil Miller wrote in message m... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CRLF in text box
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
CRLF in text box
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
CRLF in text box
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 -- Regards. Tom Ogilvy Neil Miller wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
CRLF in text box
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get SQL data to show CRLF instead of question marks? | Excel Discussion (Misc queries) | |||
Cut and Paste Text with CRLF into Single Cell | Excel Discussion (Misc queries) | |||
Making tab do a CRLF when reaching end of a range | Excel Discussion (Misc queries) | |||
HELP - How to replace CRLF with a character withing an excel column | Excel Discussion (Misc queries) | |||
extracting text from within a cell - 'text to rows@ equivalent of 'text to columns' | Excel Programming |