Unexpected result

[Patrick C. Simonds]

Can anyone tell me why the code below does not return the text in TextBox7
with the (360) in front of it. All it returns is the text of TextBox7


PhoneNum = Me.TextBox7.Text

If Len(PhoneNum) = 8 Then
FormatNum = "(360) " & (PhoneNum)
GoTo Continue
End If

 

[Mike H]

Hi,

This gives me both phonenum and formatnum provided it is 8 characters long

Sub PhoneNum()

PhoneNum = Me.TextBox7.Text
If Len(PhoneNum) = 8 Then
formatnum = "(360) " & (PhoneNum)
End If

MsgBox formatnum
MsgBox PhoneNum
End Sub

Mike

 

[Rick Rothstein]

What is it you are typing into TextBox7? I ask because your test is for an
eight character string of text; but, based on the name you gave the variable
you are assigning the content to (namely, PhoneNum), I would have expected
the test to be a seven character string of text. So, if you are in fact
typing in 7 characters, then the test for 8 characters will fail and (360)
would not be prepended to the contents of TextBox7.

 

[Patrick C. Simonds]

I am trying to create a series of if statements to produce a final phone
number of (360) 555-4412.

If they enter 8 characters (555-4412) then I just need to add they (360). If
they enter 7 characters (5554412) then I want to add the (360) and put a
hyphen between the 5 and the 4.

 

[ShaneDevenshire]

Hi Patrick,

You code words just fine for me. If there is a problem it is in the
Continue routine which you haven't shown us.

To what event on the form is this code attached? To an cmdOK_Click event?

 

[Rick Rothstein]

For the entry type you are describing (3 digits, a dash, 4 digits), your
posted code works fine for me. So, if it isn't working for you, then
something somewhere else in your code must be interfering with it. I think
you will have to show us more of your code if you want to get this issue
resolved.

 

[Patrick C. Simonds]

Thanks for your time. I have it up and running now.

Not a pro at this but with the help of the code I got through this group and
my and my own modifications I have come up with what you see bellow. If
anyone has tips on streamlining this I happily accept all advise. This is
intended to look at a phone number entered in TextBox7 (you can never be
sure they will enter the number correctly) and return the required format.


Private Sub MultiPage1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

PhoneNum = Me.TextBox7.Text

If Len(PhoneNum) = 1 Then 'Checks for a correct amount of numbers
MsgBox "Please enter a correct Phone Number"

ElseIf Len(PhoneNum) = 2 Then 'Checks for a correct amount of numbers
MsgBox "Please enter a correct Phone Number"

ElseIf Len(PhoneNum) = 3 Then 'Checks for a correct amount of numbers
MsgBox "Please enter a correct Phone Number"

ElseIf Len(PhoneNum) = 4 Then 'Checks for a correct amount of numbers
MsgBox "Please enter a correct Phone Number"

ElseIf Len(PhoneNum) = 5 Then 'Checks for a correct amount of numbers
MsgBox "Please enter a correct Phone Number"

ElseIf Len(PhoneNum) = 6 Then 'Checks for a correct amount of numbers
MsgBox "Please enter a correct Phone Number"

ElseIf Len(PhoneNum) = 7 Then 'Changes 5554125 to 555-4125
formatnum = Left(PhoneNum, 3) & "-" & Right(PhoneNum, 4)

ElseIf Len(PhoneNum) = 8 Then 'Passes unchaged the phone number 555-4125
formatnum = (PhoneNum)

ElseIf Len(PhoneNum) = 9 Then 'Passes unchaged the phone number 555-4125
MsgBox "Please enter a correct Phone Number"

ElseIf Len(PhoneNum) = 10 Then 'Changes 3605554125 to (360) 555-4125
formatnum = "(" & Left(PhoneNum, 3) & ") " & Mid(PhoneNum, 4, 3) & _
"-" & Right(PhoneNum, 4)

ElseIf Len(PhoneNum) = 11 Then 'Checks for a correct amount of numbers
MsgBox "Please enter a correct Phone Number"

ElseIf Len(PhoneNum) = 12 Then 'Changes 360-555-4125 to (360) 555-4125
formatnum = "(" & Left(PhoneNum, 3) & ") " & Mid(PhoneNum, 5, 3) & _
"-" & Right(PhoneNum, 4)

ElseIf Len(PhoneNum) = 13 Then 'Checks for a correct amount of numbers
MsgBox "Please enter a correct Phone Number"

ElseIf Len(PhoneNum) = 14 Then 'Passes unchaged the phone number (360)
555-4125
formatnum = (PhoneNum)

End If

TextBox7.Value = formatnum

End Sub

 

[Rick Rothstein]

Your If..Then filters will not handle all incorrect entries. One example
(there are others), it will allow 1-23456 to be considered a valid phone
number. I would probably have written it something like this..

Private Sub MultiPage1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim X As Long
Dim PhoneNum As String
Dim TempPhoneNum As String
PhoneNum = Me.TextBox7.Text
TempPhoneNum = Space(Len(PhoneNum))
For X = 1 To Len(PhoneNum)
If IsNumeric(Mid(PhoneNum, X, 1)) Then
Mid(TempPhoneNum, X) = Mid(PhoneNum, X, 1)
End If
Next
PhoneNum = Format(Replace(TempPhoneNum, " ", ""), "(###) ###-####")
If Not PhoneNum Like "(*) ###-####" Then
MsgBox "Please enter a correct Phone Number"
Cancel = True
Me.TextBox7.SetFocus
Else
TextBox7.Value = Replace(PhoneNum, "() ", "")
End If
End Sub

Now I recognize there are some constructions above which you will be
unfamiliar with... check them out in the help files and try and work out how
they function... if you still have questions after that, feel free to post
back to this thread and ask them.