data type

[king_jon85]

how do make a textbox numerical values only or make them a telephone number format. help would be very much appreciated.

[DKenny]

The following code will only allow numeric values in TextBox1. It will allow a leading minus sign and will also allow a single decimal point.
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
With Me.TextBox1
Select Case KeyAscii
Case ASC("0") To ASC("9")
Case ASC("-")
If InStr(1, .Text, "-") > 0 Or .SelStart > 0 Then
KeyAscii = 0
End If
Case ASC(".")
If InStr(1, .Text, ".") > 0 Then
KeyAscii = 0
End If
Case Else
KeyAscii = 0
End Select
End With
End Sub

When you say telephone format, what do you mean? (it can vary by country)

[king_jon85]

cheers for the info. i forgot to mention that i am using vba so can i use that code in vba. the telephone number is just the format in an access database.

[DKenny]

This is vba code, so you will be OK there, you will just need to change the procedure name, replacing TextBox1 with the name of your textbox.

Access telephone formats can vary depending on the regional setting, I think. Can you give me an example of the format you need? e.g. (xxx) xxx-xxxx

[king_jon85]

(99999) 099099, that sorta range.

[salvelinus]

For phone format, in the InputMask property of the textbox, under the Data tab,
enter:
!\(999") "000\-0000;;_
or select/modify as needed from the dropdown tab. 9's mean area code is optional, 0's are required numbers. This will force entry in that format, it's not the format of the field in the table; you'd have to set that in the table itself.

[king_jon85]

there aint a data tab. so i still aint got a clue on the telephone number part. as i said i am using vba within excel.

[DKenny]

(99999) 099099
So let me ask a few more Q's on this format.
Do you want the braces () to be added automatically or do you want the user to entrer them?
Will the area-code always be 5 digits?
Will the phone number always be 6 digits?

as i said i am using vba within excel. BTW, you never mentioned that you were using Excel ;)

[king_jon85]

oh soz dude. i am using vba in excel. yes i want them format. with brackets and 5 digits for area and 6 for phone number. but if brackets is a problem it dont matter to much.

[DKenny]

Do you want the braces () to be added automatically or do you want the user to entrer them?

[DKenny]

This should do it.
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
With TextBox1
Select Case KeyAscii
'Only allow numeric values
Case Asc("0") To Asc("9")

'If its the first character
'Add the opening brace
If .SelStart = 1 Then
.Value = "(" & .Value
End If

'If it is the sixth character
'(five digits plus the opening brace)
'Add the closing brace
If .SelStart = 6 Then
.Value = .Value & ") "
End If

'Only allow 14 characters max
'2 braces
'5 area code
'1 space
'6 number
If .SelStart > 14 Then
KeyAscii = 0
End If

Case Else
KeyAscii = 0
End Select
End With
End Sub

[king_jon85]

automatically.

[king_jon85]

thank u very much mate. i will try it later. cheers for the help people.