[RESOLVED] phone number

[jbaldwinroberts]

this codes validates phone numbers so if the user inputs the number in as 01159313481 or 0115 9313481 it accepts it and anything else it dosnt. the only problem i have is that it dosnt check if the space is in the right place, for example the user can put in 01159 313481 and it is accepted, any ideas?

Private Sub TextBox4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
TextBox4.Text = Trim(TextBox4.Text)

If TextBox4.TextLength = 11 Then
Dim position
position = InStr(5, TextBox4.Text, " ")
If position = 0 Then
spacedpost = TextBox4.Text
TextBox4.Text = Mid(spacedpost, 1, 4) & " " & Mid(spacedpost, 5, Len(spacedpost) - 4)
Else: MsgBox TextBox4.Text & " is not a valid mobile number. Please enter a valid 12 digit mobile number."
TextBox4.Text = ""
End If
Exit Sub
ElseIf TextBox4.TextLength = 12 Then
Dim position1
position1 = InStr(5, TextBox4.Text, " ")
If position1 = 0 Then
MsgBox TextBox4.Text & " is not a valid mobile number. Please enter a valid 12 digit mobile number."
TextBox4.Text = ""
ElseIf position = 1 Then
End If
Exit Sub
Else: MsgBox TextBox4.Text & " is not a valid mobile number. Please enter a valid 12 digit mobile number."
TextBox4.Text = ""
TextBox4.SetFocus
Cancel = True
End If

End Sub

any help is much appreciated
Joe

[RobDog888]

I say remove all spaces, check for valid length, then reformat in the correct space format and be done.

[jbaldwinroberts]

how would i remove all the spaces? i also want to check that it starts with a 0. how would i do this?

[RobDog888]

Use the Replace function passing the string, what to look for (space) and replace with a nullstring.

[jbaldwinroberts]

and checking that it starts with a 0??

[RobDog888]

If that is a validation criteria, then yes, plus any other criteria that will make the format valid.

[jbaldwinroberts]

i now have this but i get an error, object rquired, do you know what i am doing wrong

Private Sub TextBox4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
 
spacedpost = Replace(TextBox4.Text, " ", "")
If spacedpost.TextLength = 11 Then
TextBox4.Text = Mid(spacedpost, 1, 4) & " " & Mid(spacedpost, 5, Len(spacedpost) - 4)
Else
MsgBox TextBox4.Text & " is not a valid mobile number. Please enter a valid 12 digit mobile number."
TextBox4.Text = ""
TextBox4.SetFocus
Cancel = True
End If
 
End Sub

what criteria would i have to use to check that the number began with a 0?/

thanks

[RobDog888]

You could use the Instr to look at position 1.

Ps, I edited your post and reformatted the code with highligh=vb code tags for easier reading.

Private Sub TextBox4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim strPhone As String
 
    strPhone = Replace(TextBox4.Text, " ", "")
 
    If Len(strPhone) = 11 And Instr(1, strPhone, "0") = 1 Then
        strPhone = Mid(strPhone, 1, 4) & " " & Mid(strPhone, 5, Len(strPhone) - 4)
        Text4.Text = strPhone
    Else
        MsgBox TextBox4.Text & " is not a valid mobile number. Please enter a valid 12 digit mobile number."
        TextBox4.Text = ""
        TextBox4.SetFocus
        Cancel = True
    End If
End Sub

[jbaldwinroberts]

if i put in 01159313481 i get a runtime error 424: object rquired, any idea why?

[RobDog888]

What line of code? Diid you use what I posted?

[jbaldwinroberts]

i used exactly what you posted and it gave me that error, it highlighted this line

Text4.Text = strPhone
in the first part of the if statement. the rest of it works though.

[RobDog888]

Arrg, my VB 6 brain is taking over lol.

Change it to TextBox4.Text and not Text4.Text.

[jbaldwinroberts]

yep that works. brilliant, thankyou very much for all your help. joe

[RobDog888]

No prob. Glad to help.