Limit the amount of text in a textbox

[Stephen_IV]

Can anyone please tell me how to limit the amount of text that can be put into a textbox? For example if I only want 10 words maximum thats it.

Thanks in advance
Stephen

 

[Tom Urtis]

Here is one way to accomplish that, assuming that words are defined by the virtue of a space separating them. Modify the code for TextBox name if it is not TextBox1.

Private Sub TextBox1_Change()
Dim Spaces As Integer
With TextBox1
Spaces = Len(.Text) - Len(Application.Substitute(.Text, Chr(32), ""))
If Spaces < 10 Then Exit Sub
Application.EnableEvents = False
.Text = Trim(.Text)
Application.EnableEvents = True
End With
MsgBox "You are limited to 10 words.", 48, "Too many words."
End Sub

 

[Norie]

Is this a textbox on a useform or worksheet?

What version of Excel are you using?

Something like this might work in Excel 2000.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim arrSentence

    arrSentence = Split(TextBox1.Text, " ")
    
    If UBound(arrSentence) > 9 Then
        MsgBox "That's more than 10 words"
    End If
    
End Sub

 

[Stephen_IV]

Thank you both. It worked perfectly!

 

[Legacy 98055]

Also, if activeX, see the MaxLen design time property.