Userform Textbox Formatting

**lilsnoop** · 02-28-2009, 08:17 PM

Could anyone offer advice on how I would go about accomplishing this task? I'm trying to format two textbox fields on my userform that when the user inputs data into it, it would automatically show the "symbols, dashes and/or spaces accordingling".

The first textbox field (TextBox9) I need to end up looking like this, with the numbers and/or letters changing by user input.
example: (MI) A000-000-000-000

The next textbox Field (TextBox10) I need to end up with this type of result:
example: (MI ID) A000-000-000-0

I was trying something like this code: but I'm not familiar enough with the formatting symbols to accomplish this task. Any help would be appreciated!

Private Sub TxtProperty_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    TextBox9 = Format(TextBox9, "(##)&#000-000-000-000")
End Sub

**Kenneth Hobson** · 02-28-2009, 08:42 PM

View your Custom numeric formats to see how they are done. Letters are $.

TextBox9.Value = Format(TextBox9.Value, "($$) $000-000-000")

**lilsnoop** · 02-28-2009, 10:09 PM

Thanks Ken, I tried looking at the custom numeric formats but it didn't help.. especially when I need parentheses in my formatting as well... It seems quotes will need to be before and after my dashes, but the rest I'm still researching. Thanks for the letter symbol!

**Leith Ross** · 03-01-2009, 02:09 AM

Hello lilsnoop,

Move your code from the Exit event to the AfterUpdate event. That should help.

**lilsnoop** · 03-01-2009, 01:52 PM

Thanks Leith,

This is what has gotten me the closest thus far:

Private Sub TextBox14_AfterUpdate()
If Len(TextBox14) = 0 Then Exit Sub
Select Case Len(TextBox1)
    Case 11
        TextBox14.Text = Format(TextBox14, "($$) $###-###-###-###")
    Case 10
        TextBox14.Text = Format(TextBox14, "($$ $$) $###-###-###-#")
    Case Else
        MsgBox ("Invalid License or ID format. Please try again."), , "Invalid Number"
        TextBox1.Text = ""
End Select
End Sub

But, if someone types MID234000000000, I was hoping that after they hit the tab button it would put it in the format (MI) D234-000-000-000, etc.
I'm thinking this isn't possible due to having spent at least 6 hours searching the web.. It appears most people want a userform textbox format for dates and/or currency related issues. Any other suggestions would be appreciated if you think this can be done.

**Leith Ross** · 03-01-2009, 03:05 PM

Hello lilsnoop,

Can you post your workbook? It would help answer some questions I have more quickly.

**Kenneth Hobson** · 03-01-2009, 03:26 PM

Try the Mid() method:

Private Sub UserForm_Initialize()
  TextBox1.Value = "MID234000000000"
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  Dim s As String
  With TextBox1
    s = .Text
    If Len(s) = 15 Then
      .Text = "(" & Mid(s, 1, 2) & ") " & Mid(s, 3, 4) & "-" & _
        Mid(s, 7, 3) & "-" & Mid(s, 10, 3) & "-" & Mid(s, 13, 3)
    End If
  End With
End Sub