Re: Limiting Size of Text Fields

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Biff (biffinpitt_at_comcast.net)
Date: 08/20/04 

Date: Fri, 20 Aug 2004 10:45:29 -0700

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
 
>-----Original Message-----
>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 work*** code module. To insert
it, right-click
>on the *** module, and choose View Code.
>Paste the code where the cursor is flashing.
>Change the references to match your work***. 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.
>
>cynnyhelen < wrote:
>> 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
>>
>>
>> ---
>> Message posted from http://www.ExcelForum.com/
>>
>
>
>--
>Debra Dalgleish
>Excel FAQ, Tips & Book List
>http://www.contextures.com/tiptech.html
>
>.
>

Quantcast