Limiting Size of Text Fields

C

cynnyhelen

Is there a way to limit the size of a text field? I know the max in
Excel is 255 characters, but I have been asked to find a way to limit a
field to 50 characters (or identify a way to notify the user that they
have exceeded 50 characters). I found a function (LEN) that counts the
characters, but it looks like this would have to be completed manually
each time and so would not be effective?

Any ideas would be appreciated...

Cynthia
 
B

Biff

Hi Cynthia!

You can use data validation to limit the number of
characters entered in a cell.

Select the cell(s) to apply this to
Goto DATA>VALIDATION
In the ALLOW dropdown select TEXT LENGHT
In the DATA dropdown select LESS THAN OR EQUAL TO
In the MAXIMUM box enter 50

Tou can choose to have an input or error msg if you want,

Biff
 
D

Debra Dalgleish

You can limit a cell to 50 characters with Data Validation. However,
users won't be notified of the problem until they press the Enter key to
complete the entry.

They would have to reduce the number of characters, and retry the entry,
until they got down to the 50 character limit.

Also, users could paste text of any length into the cell.

Instead, you could use code to reduce the text to 50 characters. For
example:

'========================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Application.EnableEvents = False
If Target.Column = 4 Then
If Target.Count > 1 Then
For Each c In Target
c.Value = Left(c.Value, 50)
Next c
GoTo exit_Handler
End If
Target.Value = Left(Target.Value, 50)
End If

exit_Handler:
Application.EnableEvents = True

End Sub
'==========================

This code goes on the worksheet code module. To insert it, right-click
on the sheet module, and choose View Code.
Paste the code where the cursor is flashing.
Change the references to match your worksheet. For example, this code
will modify entries in column 4 (column D). You could change that to a
different column or to a specific row.
 
R

RagDyer

<<"However, users won't be notified of the problem until they press the
Enter key to complete the entry.">>

How about the "Input Message" Deb, which can be utilized to display a
warning, as soon as the cell is selected, that the cell is limited to only
50 characters?
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

You can limit a cell to 50 characters with Data Validation. However,
users won't be notified of the problem until they press the Enter key to
complete the entry.

They would have to reduce the number of characters, and retry the entry,
until they got down to the 50 character limit.

Also, users could paste text of any length into the cell.

Instead, you could use code to reduce the text to 50 characters. For
example:

'========================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Application.EnableEvents = False
If Target.Column = 4 Then
If Target.Count > 1 Then
For Each c In Target
c.Value = Left(c.Value, 50)
Next c
GoTo exit_Handler
End If
Target.Value = Left(Target.Value, 50)
End If

exit_Handler:
Application.EnableEvents = True

End Sub
'==========================

This code goes on the worksheet code module. To insert it, right-click
on the sheet module, and choose View Code.
Paste the code where the cursor is flashing.
Change the references to match your worksheet. For example, this code
will modify entries in column 4 (column D). You could change that to a
different column or to a specific row.
 
D

Debra Dalgleish

You can warn users that the cell is limited to 50 characters, but do you
expect them to count each letter as they type?

There's nothing built in that will alert them when they've reached the
limit.
 
R

RagDyeR

You're right, but with 50 being the *maximum* allowed (not a required
amount), I'd assume that using less would always suffice.
And with the warning in mind while typing, it's probably sufficient
(although I can think of two of my users who wouldn't stop at a 100)<bg>
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

You can warn users that the cell is limited to 50 characters, but do you
expect them to count each letter as they type?

There's nothing built in that will alert them when they've reached the
limit.
 
B

Biff

While the code may be an alternative, it still does not
provide for a warning or notice to the user. It simply
truncates the cell lenght to 50 characters. I still think
that a message should be displayed, using any method of
choice Eg: msg box in the code, a normal cell comment , or
even a data validation input msg.

Also, the code, as the DV method, do not prevent the user
from typing more than 50 characters. I think just
arbitrarily truncating the string at 50 characters without
notifying the user before hand really is not the best way
to go.

Biff
 
D

Debra Dalgleish

One advantage of the code is that it doesn't leave the user trapped in
the cell if the entry is longer than 50 characters.

You could add a comment at the top of the column, indicating the cell
limit. And something in the code that alerts the user, if the limit has
been exceeded, is a good idea.

The following code displays a message, stores the long string in a cell
comment, then truncates the text:

'====================================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Dim cmt As Comment
Dim str As String
Dim msg As String
str = "50 char limit exceeded:" & Chr(10)
msg = "Max 50 characters"

Application.EnableEvents = False
If Target.Column = 4 Then
If Target.Count > 1 Then
For Each c In Target
If Len(c.Value) > 50 Then
Set cmt = c.Comment
If cmt Is Nothing Then
c.AddComment Text:=str & c.Value
Else
cmt.Text Text:=cmt.Text & Chr(10) _
& str & c.Value
End If
c.Value = Left(c.Value, 50)
MsgBox msg
End If
Next c
Else
If Len(Target.Value) > 50 Then
Set cmt = Target.Comment
If cmt Is Nothing Then
Target.AddComment Text:=str & Target.Value
Else
cmt.Text Text:=cmt.Text & Chr(10) _
& str & Target.Value
End If
Target.Value = Left(Target.Value, 50)
MsgBox msg
End If
End If
End If
Application.EnableEvents = True
End Sub
'============================
 
C

cynnyhelen

Thanks everyone for your info. I really appreciate your assistance. I
searched everywhere I could think of and could not find this
info--probably wasn't using the right search criteria. I think the data
validation will work for what we are doing.

Cynthia
 
Top