Custom Phone Number Format

[IvenBach]

I searched through the previous posts but didn't come across anything similar. I am looking for a custom number format for phone numbers that will allow extensions to be typed in. I am trued using the standard phone format [<=9999999]###-####;(###) ###-#### but would like to be able to add extensions on the end. The problem that I have is that when I do so, it takes the numbers from the Area Code and replaces them with 0's if I go over the Number of extensions.

Example Input of 5555555555123 with a format of [<=9999999999](###) ###-####;(###) ###-####"xt"### gives me (555) 555-5555xt123. The problem I have is when I have less than a 123 extension or more less than gives me (55) 555-5555xt512 or (5555) 555-5551xt234 respectively. Anyone see a workaround? I have tried 0's and # but nothing seems to fit. I don't have enough exposure to custom formats and am ready to learn.

 

[IvenBach]

*Bump* Any ideas?

 

[lenze]

You can do this with a combination of "Custom Styles" and a WorkSheet Change Event
Choose Format>Styles and create a custom style for each possible number of digits that can be used. So for a phone number with 13 digits, including a 3 digit ext, create a style "Phone13", with numbers custom formatted

(###)###-####"xt"###

For numbers with a 2 digit extention, create a style "Phone12" formatted as

(###)###-####"xt"##

Do the same for other possible lengths of your phone numbers. Now use this code

Private Sub WorkSheet_Change(ByVal Target as Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 3 Then Exits Sub ' Set to column "C" Change as needed
Select Case LEN(Target)
     Case 13 : Target.Style = "Phone13"
     Case 12 : Target.Style = "Phone12"
     'etc
End Select
End Sub

HTH
lenze

 

[IvenBach]

I created the Styles and have inserted your code into the worksheet but am having an error occur. At the line If Target.Column <> 6 Then Exits Sub I have an error come up as Compile Error: Syntax Error. After trying to fix had it come up with Compile Error: Expected: expression if I keep the comment that followed on the same line. Does this seem like something that could be fixed? Also, what does this error mean exactly and what does your solution do to fix it?
Thank you for this help as I am just starting with Macros.

 

[lenze]

Sorry, I had a typo

If Target.Column <> 6 Then Exits Sub

should be

If Target.Column <> 6 Then Exit Sub

lenze

 

[IvenBach]

Works like a charm. Now I have another question. Please bear with me. I understand the Target.Column <> 3 but I am not familiar with the Target.Count > 1 portion. How does that work.
Also for the Case portion I am new to VBA so am I correct in assuming that is a way you can easily set styles or have or have I made an a$$ out of U & Mi? I'm looking for a little instruction on both of these. Btw thank you

 

[lenze]

Good questions: The Target.Count >1 limits the macro to only firing if 1, and 1 cell only, is changed. This allows you to mass delete from the column without crashing the macro.

The case format is really a sophisticated IF statement. You can use all kinds of criteria. Some examples:

Select Case Target.Column
   Case 1: Code for Target in column "A"
Select Case Target
   Case 3,5,7: Code for Target having a value of 3,5 or 7
Select UCase9Target)
    Case : "MARY": Code for if Target = Mary
Select Case Len(Target)
    Case 5: code if Target length is 5 characters
Select Case Target
    Case <5: Case for Target < 5
    Case <10: Case for Target < 10. If after the < 5, this will be for 5,6,7,8,9

Numerous other examples. Just keep in mind, once a case is met, the select operation ceases, just like an IF formula when it is True
lenze

 

[IvenBach]

Target.count & Target.column I understand. Case - still fuzzy but I get the concept. May have to play around with it some more. Thanks for the second helping.

 

[IvenBach]

I have one additional stumbling block. I am trying to have shipping tracking numbers automatically made into hyperlinks but am not very successful. Here is the code that I currently have.

Sub UPS_Tracking_Number(ByVal Target As Range) '
Dim TrackNum As String

If Target.Count > 1 Then Exit Sub
If Target.Column <> 11 Then Exit Sub ' Set to column needed "K"
If Len(Target) <> 18 Then Exit Sub
    TrackNum = ActiveCell.FormulaR1C1
    Selection.Hyperlinks(1).Address = "http://wwwapps.ups.com/WebTracking/processRequest?HTMLVersion=5.0&Requester=NES&AgreeToTermsAndConditions=yes&loc=en_US&tracknum=" & TrackNum

End Sub

I can have it run as a macro if i take out the ByVal Target portion but it chokes on the Selection.Hyperlink section. When I reinsert the ByVal and modify a value in K there doesn't seem to be any reaction. Any help here would be very much appreciated.
On a side-note what exactly does the ByVal Target As Range do? All I can understand so far is it makes it auto execute and you can't manually run it.

 

[lenze]

You're going to have to combine it with-in your other Change Event

Private Sub WorkSheet_Change(ByVal Target as Range)
If Target.Count > 1 Then Exit Sub
Select Case Target.Column
Case 3
  Select Case Len(Target)
       Case 13 : Target.Style = "Phone13"
       Case 12 : Target.Style = "Phone12"
       'etc
  End Select
Case 11
   If Len(Target) <> 18 Then Exit Sub
        TrackNum = ActiveCell.FormulaR1C1
        Selection.Hyperlinks(1).Address = "http://wwwapps.ups.com/WebTracking/proces
   End If
Case Else
End Select
End Sub

lenze