VBA code for change event in text box to limit characters to 69

[bigrig078]

Hello all! I need help with a VBA code for a text box change event that will limit the number of characters per line in a text box. Their is no limit to the number of lines just need to limit the characters per line in a text box to 69 please help!!! Also it will have to carry over the entire word if it's over 69 characters. For instance if the last word you were typing was "automatic" and the 69 character is "m" I need it to carry the whole word "automatic" to the next line and not cut the word off at "auto", and carry over the "matic" to the next line. It will have to carry over the entire word to the next line. This is the final code I need for my project...please help this is extremely important in the form I'm working on!

[gmayor]

What sort of text box?

[gmaxey]

Content Controls don't have a change event and the neither the change event in ActiveX or Userform text boxes has a line length property. When you are typing the event has no idea if you are on line 1 with 10 characters or line 10 with 1 character. You may be able to cobble together something that will form an array based the number of Chr(10) (lines), and then compare the text length of those lines to your condition, but resolving how to redefine any existing text will be difficult if not impossible.

[bigrig078]

A form control text box in MS word. I'm new to VBA started a project and I'm slowly figuring out how it all works!

[bigrig078]

Greg thanks for your reply! What would the array Code look like? I'm new to VBA and still figuring things out any help would be greatly appreciated! I have a macro in MS word that pops up a user form with several other fields and a textbox that will comprise the body of the email. I'd like limit the text box to only 69 characters per line any ideas?

[gmaxey]

rivate Sub TextBox1_Change()
Dim arrLines() As String
Dim lngIndex As Long
arrLines = Split(TextBox1.Text, Chr(10))
'MsgBox UBound(arrLines) + 1 'how many lines
For lngIndex = 0 To UBound(arrLines)
If Len(arrLines(lngIndex)) > 15 + 1 Then '69 + 1 Then
Beep
'Do something
End If
Next
End Sub

[bigrig078]

Greg thanks for the code! I tried this and a message box pops up after 69 characters are entered and it will not automatically roll the text to the next line. Do you have a code that will automatically roll the text to the next line after it reaches 69 characters and allow you to keep typing?

[gmaxey]

No I didn't. I thought you were the one who was learning to write code

I would probably use the KeyPress Event. This is not going to meet your state goal however:


Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim arrLines() As String
Dim lngIndex As Long
Dim k
k = KeyAscii
arrLines = Split(TextBox1.Text, Chr(10))
For lngIndex = 0 To UBound(arrLines)
If Len(arrLines(lngIndex)) > 69 Then
Debug.Print Asc(Right(arrLines(lngIndex), 1))
If Not Asc(Right(arrLines(lngIndex), 1)) = 13 Then
KeyAscii = 0
TextBox1.Text = TextBox1.Text & Chr(10) & Chr(k)
Beep
End If
End If
Next
End Sub

[bigrig078]

Greg thanks the code works great! you are the best exactly what I needed thank you so much!!!!