Text box
 

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

Text box
 

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

"KimberlyC" wrote in message
...
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

Text box
 

Thanks!
That did the trick!!
"Rob van Gelder" wrote in message
...
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

"KimberlyC" wrote in message
...
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