Format textbox for telephone number

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,813
Office Version
  1. 2007
Platform
  1. Windows
Morning,

I have a userfrom of which a TextBox7 is a telephone number.

My worksheet database in column W has the customers telephone numbers.
TextBox7 is where these telephone numbers are shown on the userform.

I enter the number on the worksheet like 01934820955 & the cells format changes it to 01934 820955
BUT
When you look at the userform you will see 1934820955

If however you type on the worksheet 01934 820955 then the userform also shows 01934 820955

I see that the userform doesnt apply the format like the worksheet does.
Please can you advise how i format the TextBox7 to show the telephone number like the worksheet.

I have tried this below but that didnt work BUT it also didnt show me an error message ??

Code:
Private Sub TextBox7_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox7.Text = Format(TextBox7.Text, "00000 000000")
End Sub
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
    Dim T As String
    T = Replace(TextBox7.Text, " ", "")
    If IsNumeric(T) And Len(T) > 10 Then
        TextBox7.Text = Format(Val(Left(T, Len(T) - 6)), "00000 ") & Right(T, 6)
    End If
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,813
Office Version
  1. 2007
Platform
  1. Windows
Hi,
Thanks for that.
When i click on various customers some show the correct telephone format BUT some dont.

I am then unable to advise if your code works ?

What do you advise so i can check.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
When i click on various customers some show the correct telephone format BUT some dont
If you want further help then you need to tell me what the problem is and then I can amend the code
The code works if the telephone number is the same pattern as you provided in post#1
Do all "valid" telephone numbers comprise 5 characters for code and 6 characters for the number ?
If not, what are the rules to apply?

Also post the code which places the cell value in the textbox
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,813
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Hi,
I am using landline phone numbers & mobile numbers.
So mobile example is 07899 827427
Landline number example is 01934 820955

On my worksheet i type either landline or mobile without space.
When i leave the cell the custom format in use puts a space in bewteen so 01934820955 becomes 01934 820955
All phone numbers are in column W on the worksheet.

In column A of the worksheet is the customers name.
I double click on customers name & the userform is then open with the textboxes etc completed.

TextBox7 is where the phone number is shown.

I have just double clicked all the customers and looked at how TextBox7 was displaying them.

Some are shown corectly,example 01934 820955 or 07899 827427
BUT
Some are shown incorrectly,
Example 1934820955 NO starting 0 and also No space in between "Landline number"

Example 7899827427 No starting 0 and also No space in between "Mobile number"

All incorrectly shown items ahve the first starting 0 missing & also No space in between Code & Number.

Rich (BB code):
Sub LoadData(ByVal ws As Worksheet, ByVal rw As Long)
    Me.txtCustomer.Value = ws.Range("A" & rw).Value
    Me.txtRegistrationNumber.Value = ws.Range("B" & rw).Value
    Me.txtBlankUsed.Value = ws.Range("C" & rw).Value
    Me.txtVehicle.Value = ws.Range("D" & rw).Value
    Me.txtButtons.Value = ws.Range("E" & rw).Value
    Me.txtKeySupplied.Value = ws.Range("F" & rw).Value
    Me.txtTransponderChip.Value = ws.Range("G" & rw).Value
    Me.txtJobAction.Value = ws.Range("H" & rw).Value
    Me.txtProgrammerCloner.Value = ws.Range("I" & rw).Value
    Me.txtKeyCode.Value = ws.Range("J" & rw).Value
    Me.txtBiting.Value = ws.Range("K" & rw).Value
    Me.txtChassisNumber.Value = ws.Range("L" & rw).Value
    Me.txtVehicleYear.Value = ws.Range("N" & rw).Value
    Me.txtPaid.Value = ws.Range("O" & rw).Value
    Me.txtInvoiceNumber.Value = ws.Range("P" & rw).Value
    Me.TextBox1.Value = ws.Range("Q" & rw).Value
    Me.TextBox2.Value = ws.Range("R" & rw).Value
    Me.TextBox3.Value = ws.Range("S" & rw).Value
    Me.TextBox4.Value = ws.Range("T" & rw).Value
    Me.TextBox5.Value = ws.Range("U" & rw).Value
    Me.TextBox6.Value = ws.Range("V" & rw).Value
    Me.TextBox7.Value = ws.Range("W" & rw).Value
    Me.ComboBoxCustomersNames.Value = ws.Range("A" & rw).Value
    
    With Me.txtCustomer
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.Value)
    End With
        
    Me.Show
End Sub


Thanks very much
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,813
Office Version
  1. 2007
Platform
  1. Windows
@Yongle for your information i have done this to maybe assist you.

I type 07899827427 into worksheet cell
I then leave the cell & customer code now shown value in cell like 07899 827427

I double click on that customer & i see number shown as 7899827427

BUT

If i type 07899 827427 with a space in the cell worksheet.
When i then check on the userform its correctly shown as 07899 827427

Maybe the custom format on the worksheet needs looking at ?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I type 07899827427 into worksheet cell
I type 07899 827427 with a space in the cell worksheet
Code below can handle BOTH

Rich (BB code):
'Replace
    Me.TextBox7.Value = ws.Range("W" & rw).Value
'With
    Dim t As String: t = Format(ws.Range("W" & rw).Value, "00000000000")
    t = Replace(t, " ", "")
    t = Format(ws.Range("W" & rw).Value, "00000000000")
    Me.TextBox7.Value = Left(t, 5) & " " & Right(t, 6)

Is there ALWAYS a number in the cell in column W
- if there is not, then add error handling to prevent the code failing
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,901
If you like what you see in the cell you could use the .Text property rather than the .Value.

VBA Code:
Me.TextBox7.Value = ws.Range("W" & rw).Text
 

Watch MrExcel Video

Forum statistics

Threads
1,122,312
Messages
5,595,427
Members
413,990
Latest member
Kher83

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top