Automatically add spaces to userform field entry

SaneMan · #1 12-05-2013, 05:05 AM

Hi all

I wondered if anyone could help with a query I have? I have a userform which users will fill in with generic information. One of these fields is to enter a national grid reference (i.e. 'SE 12345 12345').

This NGR should always be entered as two letters (SE) then a space and then either 4 or 5 numbers, then a space and then again 4 or 5 more numbers. The amount of numbers depends on how accurate we need the NGR to be and varies case by case.

I'm trying to add a code in which will automatically convert 'SE1234512345' or 'SE12341234' so that they are correctly spaced. However I have no clue how to do this, or if it's even possible.

Anyone have any ideas?

Thanks in advance!

Antony

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.

SaneMan · #3 12-06-2013, 01:08 AM

That works perfectly! Thanks very much for your time and the help.

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