Setting Field Size in Excel 2003

[michelle]

I am trying to modify a form created by a co-worker. He has it set to type as
many characters in a field as a user wants but then gives a warning when you
hit enter letting you know that you have used more than the allowed number of
characters for that field.

Is there a way to set up each field to stop typing once the maximum number
of characters have been reached rather than having to go back and delete
characters once the typing is done?

Thanks for any assistance you may be able to provide on this subject!

 

[Gord Dibben]

No

You could set up event code to truncate anything over a certain number of
characters after user hits ENTER key.

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) > 20 Then
.Value = Left(.Value, 20)
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

 

[michelle]

Thanks Gord...I'll give it a try

No

You could set up event code to truncate anything over a certain number of
characters after user hits ENTER key.

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) > 20 Then
.Value = Left(.Value, 20)
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