limited value in a cell ?

[Tomek]

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

 

[Jim Thomlinson]

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

 

[Jim Thomlinson]

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

 

[keepITcool]

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 :

 

[Tomek]

Thank you very much !!!!

Tomek