Automatically add spaces to userform field entry

macropod · #2 12-05-2013, 08:14 AM

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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Userform Code not quite right - help please	vbanovice	Word VBA	1	09-29-2013 09:20 PM
Forms - Repeating or Duplicate Field of First Entry?	Constitution	Word	5	09-04-2013 08:26 PM
Form field selection allows entry into form text box	David C	Word	1	10-24-2012 04:53 AM
double spaces in field ref (bookmarks)	GLENCOE	Word	3	10-25-2011 07:35 AM
Form field to automatically be added to header?	razberri	Word VBA	3	02-22-2010 03:48 PM