Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Setting Field Size in Excel 2003
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Setting Field Size in Excel 2003
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 On Tue, 9 Sep 2008 08:54:01 -0700, michelle wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Setting Field Size in Excel 2003
Thanks Gord...I'll give it a try ;)
"Gord Dibben" wrote: 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 On Tue, 9 Sep 2008 08:54:01 -0700, michelle wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting field size and type | Excel Discussion (Misc queries) | |||
Excel 2003 - setting the Comment font size default | Excel Worksheet Functions | |||
custom size (page size) missing in excel 2003 | Setting up and Configuration of Excel | |||
How do I unlimit field size in using Excel Pivot Tables? | Excel Discussion (Misc queries) | |||
Setting default pivot table field setting to "sum" | Excel Discussion (Misc queries) |