Text box

  • Thread starter Thread starter KimberlyC
  • Start date Start date
K

KimberlyC

Hi

I have inserted a text box into my excel spreadsheet.
I've set it to use the enter key to move to the next line and set it to
have multiple lines.
Is there a way to limit the number of lines a user can fill?

I know you can set character limits...but I need to set line limits so that
the entire box is shown when printed...and I can set it to grow bigger...as
my sheet needs to fit on one page when printed

Thanks in advance for your help!!!
Kimberly
 
Kimberly,

A very crude way of doing it as follows:

Private Sub TextBox1_Change()
Const cMaxLines = 5
Dim lngSelStart As Long, strTemp As String, i As Long, j As Long,
lngTextLen As Long

With TextBox1
If .LineCount > cMaxLines Then
lngSelStart = .SelStart
j = .LineCount - cMaxLines
strTemp = .Text
lngTextLen = Len(strTemp)
For i = lngTextLen To 1 Step -1
If Mid(strTemp, i, 1) = vbLf Then
j = j - 1
If j = 0 Then
.Text = Mid(strTemp, 1, i - 2)
.SelStart = lngSelStart
Exit For
End If
End If
Next
End If
End With
End Sub

Rob
 
Thanks!
That did the trick!!
Rob van Gelder said:
Kimberly,

A very crude way of doing it as follows:

Private Sub TextBox1_Change()
Const cMaxLines = 5
Dim lngSelStart As Long, strTemp As String, i As Long, j As Long,
lngTextLen As Long

With TextBox1
If .LineCount > cMaxLines Then
lngSelStart = .SelStart
j = .LineCount - cMaxLines
strTemp = .Text
lngTextLen = Len(strTemp)
For i = lngTextLen To 1 Step -1
If Mid(strTemp, i, 1) = vbLf Then
j = j - 1
If j = 0 Then
.Text = Mid(strTemp, 1, i - 2)
.SelStart = lngSelStart
Exit For
End If
End If
Next
End If
End With
End Sub

Rob
Click to expand...
 
Back
Top