Hi Antony,
It's easy enough to automatically space when there's two letters followed by 8 or 10 digits, but impossible to do reliably when there's 9 digits. Assuming you can't actually have those, you could use code like:
Code:
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim StrIn As String, StrOut As String
StrIn = Trim(Me.TextBox1.Text)
If (Len(StrIn) < 10) Or (Len(StrIn) > 12) Or (Len(StrIn) Mod 2 = 1) Then
MsgBox "Incomplete Data", vbExclamation
Me.TextBox1.SetFocus
Exit Sub
End If
If Mid(StrIn, 3, 1) = " " And Mid(StrIn, (Len(StrIn) - 2) / 2 + 1, 1) = " " Then Exit Sub
StrIn = Replace(Me.TextBox1.Text, " ", vbNullString)
StrOut = Left(StrIn, 2) & " " & Mid(StrIn, 3, (Len(StrIn) - 2) / 2) & _
" " & Mid(StrIn, (Len(StrIn) - 2) / 2 + 3, (Len(StrIn) - 2) / 2)
Me.TextBox1.Text = StrOut
End Sub
where TextBox1 is the input TextBox name. Note that, with the above code, there's no need for the user to input the spaces.