Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help needed please
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help needed please
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
More help needed :-( | Excel Worksheet Functions | |||
help needed | Excel Worksheet Functions | |||
R2 Help Needed | Excel Discussion (Misc queries) | |||
Help needed... | Excel Discussion (Misc queries) | |||
Some help needed | Excel Worksheet Functions |