Format textbox for telephone number

[ipbr21054]

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 ??

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

 

[Yongle]

    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]

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]

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]

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.

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]

@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]

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

'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]

If you like what you see in the cell you could use the .Text property rather than the .Value.

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

 

[ipbr21054]

Thanks very much