Phone numbers

[Patrick C. Simonds]

I found the code below on-line (writen by Dick Kusleika) which works fine if
I click on another TextBox but if I click on my Finished Button the code
does not run. Any ideas what can be done to solve this proble?

Private Sub TextBox7_Exit(ByVal Cancel As MSForms.ReturnBoolean)

PhoneNum = Me.TextBox7.Text
If Len(PhoneNum) = 7 Then
FormatNum = "(360) " & Left(PhoneNum, 3) & "-" & Right(PhoneNum, 4)
ElseIf Len(PhoneNum) = 10 Then
FormatNum = "(" & Left(PhoneNum, 3) & ") " & Mid(PhoneNum, 4, 3) & _
"-" & Right(PhoneNum, 4)
Else
MsgBox "Not a Valid Phone Number"
Cancel = True
End If
Me.TextBox7.Text = FormatNum

End Sub

 

[Gary Keramidas]

just a guess, but add a line of code such as this as your first line of code
finish button:

Private Sub CommandButton1_Click()
Me.TextBox1.SetFocus
'then the rest of the code
end sub

 

[Patrick C. Simonds]

Thanks, but I am afraid that did not do it.

just a guess, but add a line of code such as this as your first line of
code finish button:

Private Sub CommandButton1_Click()
Me.TextBox1.SetFocus
'then the rest of the code
end sub

 

[Gary Keramidas]

worked for me, is your form modal or modeless?

 

[Gary Keramidas]

i created a userform, added 7 textboxes and a command button. added this code
and it worked. not sure what else you're doing, but try this on a new user form
and see if it works.

Private Sub CommandButton1_Click()
Me.TextBox1.SetFocus
End Sub

Private Sub TextBox7_Exit(ByVal Cancel As MSForms.ReturnBoolean)
PhoneNum = Me.TextBox7.Text
If Len(PhoneNum) = 7 Then
FormatNum = "(360) " & Left(PhoneNum, 3) & "-" & Right(PhoneNum, 4)
ElseIf Len(PhoneNum) = 10 Then
FormatNum = "(" & Left(PhoneNum, 3) & ") " & Mid(PhoneNum, 4, 3) & _
"-" & Right(PhoneNum, 4)
Else
MsgBox "Not a Valid Phone Number"
Cancel = True
End If
Me.TextBox7.Text = FormatNum
End Sub

 

[Patrick C. Simonds]

I have tried it as both Modal and Modeless.

worked for me, is your form modal or modeless?

 

[Gary Keramidas]

even without any code for the commandbutton, the format of textbox7 changes as
soon as i click it.

 

[Patrick C. Simonds]

Think I know what the problem is (but not the solution).

I did as you suggested and created a new UserForm and it worked fine. So I
went back to try and see what by problem was.

Well TextBox7 is located on Page2 of a MultiPage control located on the
UserForm and the Finished button is located on the UserForm. If I click on
anything on any of the 3 pages of the MultiPage control everything works.
But, when I click on the Finished Button it does not.

 

[Gary Keramidas]

not sure, try adding your code to the commandbutton click event

PhoneNum = Me.TextBox7.Text
If Len(PhoneNum) = 7 Then
FormatNum = "(360) " & Left(PhoneNum, 3) & "-" & Right(PhoneNum, 4)
ElseIf Len(PhoneNum) = 10 Then
FormatNum = "(" & Left(PhoneNum, 3) & ") " & Mid(PhoneNum, 4, 3) & _
"-" & Right(PhoneNum, 4)
Else
MsgBox "Not a Valid Phone Number"
Cancel = True
End If
Me.TextBox7.Text = FormatNum

 

[Rick Rothstein]

Assuming your MultiPage control is named MultiPage1, add this Exit event
procedure for it to your UserForm's code window...

Private Sub MultiPage1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If MultiPage1.SelectedItem.ActiveControl Is TextBox7 Then
PhoneNum = Me.TextBox7.Text
If Len(PhoneNum) = 7 Then
FormatNum = "(360) " & Left(PhoneNum, 3) & _
"-" & Right(PhoneNum, 4)
ElseIf Len(PhoneNum) = 10 Then
FormatNum = "(" & Left(PhoneNum, 3) & ") " & _
Mid(PhoneNum, 4, 3) & "-" & Right(PhoneNum, 4)
Else
MsgBox "Not a Valid Phone Number"
Cancel = True
End If
Me.TextBox7.Text = FormatNum
End If
End Sub

 

[keiji kounoike]

I would check a phone number in other place, i mean to check it using a
function that check a phone number and place the function in both Sub
TextBox7_Exit() and Sub Finishedbutton_Click(). i'm not sure if this
works for you, but this is a example.

Private Sub TextBox7_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim s
s = CheckPhonenum(Me.TextBox7.Text)
If IsError(s) Then
MsgBox "Not a Valid Phone Number"
Cancel = True
Else
Me.TextBox7.Text = s
Cancel = False
End If
End Sub

Private Sub CommandButton4_Click()
'Change CommandButton4 to your Finished button object name
Dim s
s = CheckPhonenum(Me.TextBox7.Text)
If IsError(s) Then
MsgBox "Not a Valid Phone Number"
'Assume TextBox7 resides in page2, so i set the value to 1
'Assume your MultiPage name as MultiPage1, Change to your object name.
Me.MultiPage1.Value = 1 '<==Change 1 to your page number-1
Me.TextBox7.SetFocus
Else
Me.TextBox7.Text = s
'Unload Me
MsgBox "a Valid Phone Number"
End If
End Sub

Private Function CheckPhonenum(ByVal num As String) As Variant
Dim Formatnum
If Len(num) = 7 Then
Formatnum = "(360) " & left(num, 3) & "-" & right(num, 4)
ElseIf Len(num) = 10 Then
Formatnum = "(" & left(num, 3) & ") " & Mid(num, 4, 3) & _
"-" & right(num, 4)
ElseIf InStr(num, "(") = 1 And InStr(num, ")") = 5 Then
If Len(trim(Mid(Replace(num, "-", ""), 6))) = 7 Then
Formatnum = num
Else
Formatnum = CVErr(xlErrNA)
End If
Else
Formatnum = CVErr(xlErrNA)
End If
CheckPhonenum = Formatnum
End Function

keiji