data type

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

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.

VB Code:

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)

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.

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

(99999) 099099, that sorta range.

For phone format, in the InputMask property of the textbox, under the Data tab,
enter:

Code:

---

!\(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.

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.

(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?

Quote:

---

as i said i am using vba within excel.

---

BTW, you never mentioned that you were using Excel ;)

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.

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

This should do it.

VB Code:

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

automatically.

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