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

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


"Patrick C. Simonds" wrote in message
...
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

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


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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


"Patrick C. Simonds" wrote in message
...
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

worked for me, is your form modal or modeless?

--


Gary


"Patrick C. Simonds" wrote in message
...
Thanks, but I am afraid that did not do it.


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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


"Patrick C. Simonds" wrote in message
...
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

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

--


Gary


"Patrick C. Simonds" wrote in message
...
Thanks, but I am afraid that did not do it.


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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


"Patrick C. Simonds" wrote in message
...
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

I have tried it as both Modal and Modeless.


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
worked for me, is your form modal or modeless?

--


Gary


"Patrick C. Simonds" wrote in message
...
Thanks, but I am afraid that did not do it.


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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


"Patrick C. Simonds" wrote in message
...
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

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

--


Gary


"Patrick C. Simonds" wrote in message
...
I have tried it as both Modal and Modeless.


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
worked for me, is your form modal or modeless?

--


Gary


"Patrick C. Simonds" wrote in message
...
Thanks, but I am afraid that did not do it.


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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


"Patrick C. Simonds" wrote in message
...
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

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" <GKeramidasATmsn.com wrote in message
...
even without any code for the commandbutton, the format of textbox7
changes as soon as i click it.

--


Gary


"Patrick C. Simonds" wrote in message
...
I have tried it as both Modal and Modeless.


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
worked for me, is your form modal or modeless?

--


Gary


"Patrick C. Simonds" wrote in message
...
Thanks, but I am afraid that did not do it.


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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


"Patrick C. Simonds" wrote in message
...
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

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

--


Gary


"Patrick C. Simonds" wrote in message
...
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" <GKeramidasATmsn.com wrote in message
...
even without any code for the commandbutton, the format of textbox7 changes
as soon as i click it.

--


Gary


"Patrick C. Simonds" wrote in message
...
I have tried it as both Modal and Modeless.


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
worked for me, is your form modal or modeless?

--


Gary


"Patrick C. Simonds" wrote in message
...
Thanks, but I am afraid that did not do it.


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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


"Patrick C. Simonds" wrote in message
...
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

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

--
Rick (MVP - Excel)


"Patrick C. Simonds" wrote in message
...
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" <GKeramidasATmsn.com wrote in message
...
even without any code for the commandbutton, the format of textbox7
changes as soon as i click it.

--


Gary


"Patrick C. Simonds" wrote in message
...
I have tried it as both Modal and Modeless.


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
worked for me, is your form modal or modeless?

--


Gary


"Patrick C. Simonds" wrote in message
...
Thanks, but I am afraid that did not do it.


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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


"Patrick C. Simonds" wrote in message
...
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

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

Patrick C. Simonds wrote:
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" <GKeramidasATmsn.com wrote in message
...
even without any code for the commandbutton, the format of textbox7
changes as soon as i click it.

--


Gary


"Patrick C. Simonds" wrote in message
...
I have tried it as both Modal and Modeless.


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
worked for me, is your form modal or modeless?

--


Gary


"Patrick C. Simonds" wrote in message
...
Thanks, but I am afraid that did not do it.


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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


"Patrick C. Simonds" wrote in message
...
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