VBA Userform

[kakiebos]

Hi All,

With a UserForm TextBox you can limit the number of characters to be used in the TextBox properties MaxLength behavior.

I want to know if your set the MultiLine to True, can you limit the number of lines that the user can enter.

For example I have a UserForm with a TextBox. I want the user to be able to enter text into a new line, but there is not supposed to be more than 4 lines of text.

Line 1 Text Good
Line 2 Text Good
Line 3 Text Good
Line 4 Text Good
Line 5 Text Will not be visible in the spreadsheet and therefor not Good.

If there is no way to limit the number of lines in the properties, is there any other way to prevent the user of going over the line limit.

For this example, it will be easy for the user to see if he is using more than the limit, but if the limit is perhaps 30 lines, I still need to be able to prevent the user of going over.

Thanks for your assistance.

 

[mikerickson]

You could put code like this in the userform's code module

Dim preText As String, preStart As Long, preLength As Long

Private Sub TextBox1_Change()
    With TextBox1
        If .LineCount > 3 Then
            .Text = preText
            .SelStart = preStart
            .SelLength = preLength
            Beep
        End If
    End With
End Sub

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    With TextBox1
        preText = .Text
        preStart = .SelStart
        preLength = .SelLength
    End With
End Sub

 

[Akuini]

Try something like this:

Private Sub TextBox1_Change()
    If UBound(Split(TextBox1, vbLf)) = 4 Then
    TextBox1 = Left(TextBox1, Len(TextBox1) - 2)
    End If
End Sub

but it only work if you type the data in the textbox, it won't work if the data is copy-pasted.

 

[Akuini]

Sorry, I was wrong, it should be about line not paragraph.
So the code in post 3 won't work.

 

[kakiebos]

This is working like a charm.

Thanks

You could put code like this in the userform's code module

Dim preText As String, preStart As Long, preLength As Long

Private Sub TextBox1_Change()
    With TextBox1
        If .LineCount > 3 Then
            .Text = preText
            .SelStart = preStart
            .SelLength = preLength
            Beep
        End If
    End With
End Sub

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    With TextBox1
        preText = .Text
        preStart = .SelStart
        preLength = .SelLength
    End With
End Sub