Edit an existing working code for UK telephone numbers

[ipbr21054]

@NoSparks
User shown above made this code for UK post codes & works great.
Post code format was EX26 9HH 4 characters then a space then 3 characters
Also BS9 4DP 3 characters then a space the 3 characters


I would like to use the same code now but for UK Telephone number.
The format for the numbers are like so.

07894 827418 "So 5 digits a space then 6 digits"

The postcode vba does work but it was allowing 2 different post codes & assume something must be removed
Thanks

Existing code.

Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Len(Me.TextBox2) > 0 Then
        Me.TextBox2.Value = Format(Replace(Me.TextBox2.Value, " ", ""), "@@@@@ @@@@@@")
    End If
End Sub

 

[NoSparks]

Ditch that code and try this

Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim str As String, i As Long
    Dim PhonNum As String
    
    If Len(Me.TextBox2) > 0 Then
        ' what was entered by user
        str = Me.TextBox2.Value
        ' remove any character that isn't a number
        For i = 1 To Len(str)
            If IsNumeric(Mid(str, i, 1)) Then
                PhonNum = PhonNum & Mid(str, i, 1)
            End If
        Next i
        
        ' you now have a string of only numbers
        
        ' deal with it based on the number of numbers
        Select Case Len(PhonNum)
            ' up to 11 digits entered
            Case Is <= 11
                Me.TextBox2.Value = Format(PhonNum, "00000 000000")
            ' more than 11 digits entered
            Case Is > 11
                MsgBox "Something wrong with entry" & vbCrLf & _
                       "too many digits entered"
                ' clear entry
                Me.TextBox2 = ""
                ' and keep focus
                Cancel = True
         End Select
    End If
End Sub

 

[ipbr21054]

Perfect much better.

Thanks very much