User form - txt field formatting numbers !!!

MasterChief

Board Regular
Joined
Feb 14, 2006
Messages
172
I am using this code to try and format a number like this 12-345-6789 but for some reason it’s not working any idea?

Private Sub nsn_Change()
If nsn.TextLenght = 2 or nsn.textlenght = 6 then
Nsn.text = nsn.text + “-“
Endif
End sub
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

CalcSux78

Well-known Member
Joined
Oct 15, 2013
Messages
1,120
Might be a typo on "TextLength"

try:
Code:
Private Sub nsn_Exit(ByVal Cancel As MSForms.ReturnBoolean)

nsn = Format(nsn, "##-###-####")
End Sub
 

MasterChief

Board Regular
Joined
Feb 14, 2006
Messages
172
Thanks works great, but is it possible to see the dashes as I start to type in the number?
 

CalcSux78

Well-known Member
Joined
Oct 15, 2013
Messages
1,120
Although not as elegant:

Code:
Private Sub nsn_Change()
Dim str$
str = Replace(nsn, "-", "")
Select Case Len(str)
    Case 1, 2
        nsn = str
    Case 3 To 5
        nsn = Mid(str, 1, 2) & "-" & Mid(str, 3)
    Case Else
        nsn = Mid(str, 1, 2) & "-" & Mid(str, 3, 3) & "-" & Mid(str, 6)
End Select
End Sub
 
Last edited:

veyselemre

Board Regular
Joined
Mar 16, 2006
Messages
76
Office Version
  1. 2010
Platform
  1. Windows
Code:
Case 0
nsn = ""
It was more beautiful when it was added.

OR
Code:
Private Sub nsn_Change()
    Dim str$
    str = Replace(nsn, "-", "")
    Select Case Len(str)
        Case 1, 2
            nsn = str
        Case 3 To 5
            nsn = Mid(str, 1, 2) & "-" & Mid(str, 3)
        Case Is > 5
            nsn = Mid(str, 1, 2) & "-" & Mid(str, 3, 3) & "-" & Mid(str, 6)
    End Select
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,481
Messages
5,601,918
Members
414,482
Latest member
morkar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top