max number of spaces/letters
 

How do I program a cell to have let say a maximum word/sentence length
of 50 spaces.:Bgr


--
willemeulen

Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands)
------------------------------------------------------------------------
willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=100198

max number of spaces/letters
 

You can do this with data validation

Select the cell(s) in question
Goto the menu DataValidation
AllowText length
Fill in the info
OK

--
Biff
Microsoft Excel MVP


"willemeulen" wrote in message
...

How do I program a cell to have let say a maximum word/sentence length
of 50 spaces.:Bgr


--
willemeulen

Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands)
------------------------------------------------------------------------
willemeulen's Profile:
http://www.thecodecage.com/forumz/member.php?userid=285
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=100198

max number of spaces/letters
 

Data-Data Validation-select Text length
set maximum to 50

or

Data-Data Validation-select Custom
in Formula window insert:
=LEN(A1)=50
(this will allow up to 50 characters in A1)

adjust address or copy to your range



On 25 Maj, 19:36, willemeulen
wrote:
How do I program a cell to have let say a maximum word/sentence length
of 50 spaces.:Bgr

--
willemeulen

Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands)
------------------------------------------------------------------------
willemeulen's Profile:http://www.thecodecage.com/forumz/member.php?userid=285
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=100198

max number of spaces/letters
 

Using data validation for this many characters would be a PITA in my
estimation.

DV won't automatically give you a maximum of 50, just a warning after you
hit the ENTER key and you have to start all over again.

I would prefer code that doesn't raise a an error if more than 50 chars are
entered, just truncates to first 50 chars.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10" 'edit to suit
' "A1,A2,B1,C5,C6" for a non-contiguous range example
Dim cell As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Len(.Value) 50 Then
.Value = Left(.Value, 50)
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste into that module, Edit the range to suit. Alt + q to return to
the Excel window.


Gord Dibben MS Excel MVP


On Mon, 25 May 2009 18:36:45 +0100, willemeulen
wrote:


How do I program a cell to have let say a maximum word/sentence length
of 50 spaces.:Bgr

max number of spaces/letters
 

"Start all over again" is an exaggeration but you do have hit "retry" then
click in formula bar and edit back down to 50 or less.


Gord

On Mon, 25 May 2009 13:44:22 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

DV won't automatically give you a maximum of 50, just a warning after you
hit the ENTER key and you have to start all over again.