Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
limited value in a cell ?
Hello!
Is there any way to limit the value we write in a cell. For example: I would like to write only phone numbers in one column of such structure (12) 111-111-111. Of course the are many different numbers. I would like to limit it that nobody could write there letters or other signs or more numbers than is in the structure. for example: (48) 121-121-252 correct (52) 1233-14-254 not correct (52)52-22-55555 not correct (56) adc-455-222 not correct (00) 000-012-987 correct Thank you very much for help. Tomek -- Tomek |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
limited value in a cell ?
When I need phone numbers I use two things. Custom formats and validation.
Use validation to restrict the user to entering integers between cretain numbers. in your case: 10000000000 and 99999999999. Now to display the number correctly use a custom format. Choose format cell - custom - now enter (##) ###-###-### HTH "Tomek" wrote: Hello! Is there any way to limit the value we write in a cell. For example: I would like to write only phone numbers in one column of such structure (12) 111-111-111. Of course the are many different numbers. I would like to limit it that nobody could write there letters or other signs or more numbers than is in the structure. for example: (48) 121-121-252 correct (52) 1233-14-254 not correct (52)52-22-55555 not correct (56) adc-455-222 not correct (00) 000-012-987 correct Thank you very much for help. Tomek -- Tomek |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
limited value in a cell ?
Sorry I did not see you final phone number... Restrict the user to a much
smaller lower limit and change the custom format to : (00) 000-000-00# HTH "Tomek" wrote: Hello! Is there any way to limit the value we write in a cell. For example: I would like to write only phone numbers in one column of such structure (12) 111-111-111. Of course the are many different numbers. I would like to limit it that nobody could write there letters or other signs or more numbers than is in the structure. for example: (48) 121-121-252 correct (52) 1233-14-254 not correct (52)52-22-55555 not correct (56) adc-455-222 not correct (00) 000-012-987 correct Thank you very much for help. Tomek -- Tomek |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
limited value in a cell ?
Tomek,
you could do it by allowing only whole numbers to be entered via data-validation. Then using a custom number format like: (00) 000-000-000 Note the values will be stored and entered as (long) numbers, not as formatted strings. Some users may not find this very logical though... However you cannot use datavalidation on strings, thus if you want your users to enter formatted strings then you'll have to write an event macro.. Not extensively tested, but seems to do the trick.. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub ElseIf Intersect(Target, Me.Range("B:B")) Is Nothing Then Exit Sub Else 'target is 1 cell in "B:B" If Len(Target) 0 And Not Target.Value Like "(##) ###-###-###" Then Beep Dim i%, sOld$, vNew sOld = Target For i = 1 To Len(sOld) Select Case Mid(sOld, i, 1) Case 0 To 9 vNew = vNew & Mid(sOld, i, 1) End Select Next vNew = CDec(Right$(vNew, 11)) Application.EnableEvents = False Target = Format(vNew, "(00) 000-000-000") Application.EnableEvents = True End If End If End Sub -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Tomek wrote : Hello! Is there any way to limit the value we write in a cell. For example: I would like to write only phone numbers in one column of such structure (12) 111-111-111. Of course the are many different numbers. I would like to limit it that nobody could write there letters or other signs or more numbers than is in the structure. for example: (48) 121-121-252 correct (52) 1233-14-254 not correct (52)52-22-55555 not correct (56) adc-455-222 not correct (00) 000-012-987 correct Thank you very much for help. Tomek |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
limited value in a cell ?
Thank you very much !!!!
Tomek -- Tomek |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
limited cell movement | Excel Worksheet Functions | |||
Anyone notice excel search within cell limited to first 1024 chars | Excel Discussion (Misc queries) | |||
Limited fraction cell format | Excel Discussion (Misc queries) | |||
Excel Add In - Limited Use? | Excel Programming | |||
Excel Add In - Limited Use? | Excel Programming |