TextBox limit

[Patrick C. Simonds]

Is there any way to limit a TextBox to only 4 lines of text? I know I can
limit the number of characters, but users will be entering a mix of capital
and lower case letters, so it is hard to know what to set the max length to.
My TextBox is set up so that a text line can not exceed the length of the
line the text will be placed on.

 

[Jay Freedman]

Is there any way to limit a TextBox to only 4 lines of text? I know I can
limit the number of characters, but users will be entering a mix of capital
and lower case letters, so it is hard to know what to set the max length to.
My TextBox is set up so that a text line can not exceed the length of the
line the text will be placed on.

No, that's not possible with a text form field. The best you can do is
to put the form field into a table cell that has been formatted with
fixed column width and an exact row height. That doesn't prevent users
from pressing Enter or otherwise inserting more characters, but the
extra won't be visible. If you use a macro to retrieve the content of
the form field, though, all the extra text will be there.

If you really must constrain the amount of text, get rid of the form
fields and use a UserForm instead
(http://www.word.mvps.org/FAQs/Userforms/CreateAUserForm.htm). The
text box controls in a UserForm allow you to examine their contents on
every keystroke (by writing code in the control's Change procedure)
and take action if the text is too long, contains invalid characters,
etc.

One bit of advice: When asking about text form fields, don't call them
text boxes -- that's something completely different (on the menu,
Insert > Text Box).

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[Patrick C. Simonds]

Sorry, I was not clear enough. I am talking about a TextBox on a UserForm.

 

[Jay Freedman]

OK, that is the important distinction.

It looks like this will do what you asked:

Private Sub TextBox1_Change()
Dim temp As String
If TextBox1.CurLine > 3 Then ' 0-based
MsgBox "Input can't be more than 4 lines."
temp = TextBox1.Text
If Right$(temp, 2) = vbCrLf Then
temp = Left$(temp, Len(temp) - 2)
Else
temp = Left$(temp, Len(temp) - 1)
End If
TextBox1.Text = temp
End If
End Sub

It doesn't matter whether the line breaks in the box occur because of
pressing Enter or because of automatic line wrapping.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[Patrick C. Simonds]

Thank you very much, that did the trick.

OK, that is the important distinction.

It looks like this will do what you asked:

Private Sub TextBox1_Change()
Dim temp As String
If TextBox1.CurLine > 3 Then ' 0-based
MsgBox "Input can't be more than 4 lines."
temp = TextBox1.Text
If Right$(temp, 2) = vbCrLf Then
temp = Left$(temp, Len(temp) - 2)
Else
temp = Left$(temp, Len(temp) - 1)
End If
TextBox1.Text = temp
End If
End Sub

It doesn't matter whether the line breaks in the box occur because of
pressing Enter or because of automatic line wrapping.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[Paul Laba]

I wanted to comment on the proposed solution to the 'detect too many lines' request, since I'm also trying to find a way to restrict the number of lines in a multi-line textbox.

The CurLine property returns the (0-based) index of the line where the insertion pointer is currently located, taking into account both explicit line breaks (vbCrLf characters inserted at the point when the user pressed Return) and implicit line breaks (points where VBA "wrapped" a word onto the next line).

The proposed solution works fine in those cases where the user is typing text with the insertion point always located after the last character in the textbox, but doesn't work correctly if the user moves the insertion point to an earlier line in the textbox. In that case, CurLine returns the index of line where the insertion pointer is located, not the index of the last line in the text box. The test to determine if CurLine exceeds the maximum no. of lines allowed may return True even though the total number of lines in the textbox exceeds the maximum allowed.

This can happen if the user moves the insertion point to earlier in the text and and then enters or pastes additional text, causing additional line breaks to be inserted.

A workaround in the Change event handler would be to first save the textbox's SelStart and SelLength properties, then set SelStart to the length of the Text property (the end of the text). CurLine would now correctly return the index of the last line in the textbox. On exit, restore the saved SelStart and SelLength properties.

 

[Paul Laba]

I came across a curious behavior of VBA in changing the TextBox control's CurLine property.

This property behaves as expected when automatic word wrapping occurs: If the next character entered causes the current word to we wrapped onto the next line, VBA increments the value of CurLine to indicate that the insertion point is now positioned on the next line.

However, if the user presses <Enter> to insert an explicit line break into the text (the character pair vbCrLf), VBA does *not* increment the value of CurLine, even though the insertion point is now positioned on the next line. Instead, an additional character must be typed before CurLine gets incremented.

To illustrate, create a 2-line high TextBox control (TextBox1) with its MultiLine property set to True.
Double-click the control to create a Change event handler. Add this single line to the handler:

If Me.TextBox1.CurLine > 0 Then Beep 'beep if 2 lines

Now run the form and enter a few characters in TextBox1. No beeps. Now press Enter. Still no beep!, even though the insertion pointer is now sitting on line 2. Now enter another character. Beep!

This means that your Change event handler will not always detect a line overflow condition at the precise moment when it occurs, in this case when the user pressed Enter to insert a new line. Worse, if the user presses Enter to add a new line to the end of the text, but never enters any text on that line, the handler will not detect the new line.

Note that the handler *does* work correctly in detecting a line overflow condition in cases where an automatic word wrap moves the insertion pointer to the next line.
To demonstrate this, run the form again and start typing characters on the first line. At the moment the insertion point moves to the second line, you'll hear a beep.


Submitted via EggHeadCafe - Software Developer Portal of Choice
SharePoint Lists In Excel Via VSTO
http://www.eggheadcafe.com/tutorial...8377c/sharepoint-lists-in-excel-via-vsto.aspx