The code below seems to work.
Copy the code and paste it into the sheet module.
(right-click the sheet tab and select View Code)
You must specify the correct Entry_Column number.
Also, the Entry_Column must be formatted as Text.
-
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

'---
Private Const Entry_Column As Long = 5 '<<<Change as necessary

Private Sub Worksheet_Change(ByVal Target As Range)
'Jim Cone - San Francisco USA - October 28, 2007
'The NumberFormat for the Entry_Column must be set to Text ("@")
On Error GoTo ExitHere
Dim N As Long
Dim str As String
If Target.Column < Entry_Column Or Len(Target(1).Value) = 0 Then
GoTo ExitHere
Else
Application.EnableEvents = False
Target(1).Font.ColorIndex = xlColorIndexAutomatic
str = "'" & Target(1).Text
Target(1).Value = str
For N = 2 To Len(str)
If Mid$(str, N, 1) Like "[!0-9+]" Then
Target(1).Characters(N - 1, 1).Font.ColorIndex = 3
Target(1).Select
MsgBox "Try Again ", vbExclamation, " The GodFather"
Exit For
End If
Next 'N
End If
ExitHe
Application.EnableEvents = True
End Sub
'---

"TheGodfather"
wrote in message
Hi all,
i would like to make a phone book in excel a data validation
constraint i want is in some rows to insert numbers only no text
allowed, and at the same time i want those numbers to be treated as
text to prohibit their calculation, i tried to format as text but then
text will be accepted , i tried data validation with the is number
function, but this wouldn't prohibit the calculation , i need a mix of
those two, to stop the user entering text and in the meanwhile treat
numbers entered as text.
must have the ability of entering area codes and
international codes like (+1) or (+30)
i want the user to be able to enter any number , the + sign of course
or the "00"(when she enters 00 , the 0's must show up)
as
a valid number is like :
+306934778585
or
+9613209845
Thanks