Input Mask?

[ironj32]

is there a way to use an input mask similar to access?

I would like for one text box txtTaxID, to automatically put the dash in after the first two numbers ie: 12-3456789. this would just be for userfriendliness. on the flip side i would like it to just be the numbers 123456789 when they click save and it gets put into a hidden spread sheet.

same would go for a txtDate: 00/00/0000 for the user. 00000000 when it gets put into the spread sheet.

also txtAnnualValue: $20,000,000 ...having the comma appear after the first three numbers, and every three numbers after that. then would end up being 20000000 in the spreadsheet.

thanks for your input!

[mdmackillop]

[VBA]Private Sub CommandButton1_Click()
Cells(1, 1) = Application.WorksheetFunction.Substitute(TextBox1, "-", "")
Cells(2, 1) = Application.WorksheetFunction.Substitute(TextBox2, "/", "")
Cells(3, 1) = Format(TextBox3.Value, "0")
Cells(3, 1).NumberFormat = "General"

End Sub

Private Sub TextBox1_AfterUpdate()
Application.EnableEvents = False
TextBox1.Text = Format(TextBox1, "00-0000000")
Application.EnableEvents = True

End Sub

Private Sub TextBox2_AfterUpdate()
Application.EnableEvents = False
TextBox2.Text = Format(TextBox2, "00/00/0000")
Application.EnableEvents = True

End Sub

Private Sub TextBox3_AfterUpdate()
Application.EnableEvents = False
TextBox3.Text = Format(TextBox3, "$#,###,##0")
Application.EnableEvents = True

End Sub
[/VBA]

[ironj32]

thanks md! now is there a way to use "place holders" or to have the symbols appear as you are typing? i tried txtTaxID_change(), but that is not working. it automatically puts in some random numbers that i cannot change.

[mdmackillop]

Not as far as I know.

[mvidas]

Hi ironj32,

This doesn't include any kind of error-checking to make sure numbers were pressed, but for your taxid and date textboxes, you could use something like:[vba]Private IsChanging As Boolean

Private Sub txtDate_Change()
If Not IsChanging Then
With txtDate
Select Case Len(.Text)
Case 2, 5: .Text = .Text & "/"
Case Is > 10: .Text = Left(.Text, 10)
End Select
End With
End If
End Sub
Private Sub txtDate_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode <> 8 Then Exit Sub 'keycode 8 is backspace
If Len(txtDate.Text) = 3 Or Len(txtDate.Text) = 6 Then 'keycode 8 is backspace
IsChanging = True
txtDate.Text = Left(txtDate.Text, Len(txtDate.Text) - 1)
IsChanging = False
End If
End Sub

Private Sub txtTaxID_Change()
If Not IsChanging Then
With txtTaxID
Select Case Len(.Text)
Case 2: .Text = .Text & "-"
Case Is > 10: .Text = Left(.Text, 10)
End Select
End With
End If
End Sub
Private Sub txtTaxID_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode <> 8 Then Exit Sub 'keycode 8 is backspace
If Len(txtTaxID.Text) = 3 Then
IsChanging = True
txtTaxID.Text = Left(txtTaxID.Text, 2)
IsChanging = False
End If
End Sub[/vba]This could be a lot more involved if you wanted it to be, but if you trust your users not to enter letters or symbols or anything this should give you the functionality you want.

Doing the same for the txtAnnualValue would be a lot more involved, MD's method should be fine for them. If you really need it to show up with the format as they're typing, let me know, but I think it would be more trouble than its worth.

Matt

[mvidas]

Another method of doing this (again for txtDate and txtTaxID) would be to use regular expressions, which would create a regexp object every time the text is changed, however it would prevent bad characters from being entered (no noticeable speed difference either):[vba]Private Sub txtDate_Change()
Dim RegEx As Object, tStr As String
Set RegEx = CreateObject("vbscript.regexp")
RegEx.Global = True
RegEx.Pattern = "\D"
tStr = RegEx.Replace(txtDate.Text, "")
txtDate.Text = Left(tStr, 2) & IIf(Len(tStr) > 2, "/" & Mid(tStr, 3, 2), "") & _
IIf(Len(tStr) > 4, "/" & Mid(tStr, 5, 4), "")
Set RegEx = Nothing
End Sub
Private Sub txtTaxID_Change()
Dim RegEx As Object, tStr As String
Set RegEx = CreateObject("vbscript.regexp")
RegEx.Global = True
RegEx.Pattern = "\D"
tStr = RegEx.Replace(txtTaxID.Text, "")
txtTaxID.Text = Left(tStr, 2) & IIf(Len(tStr) > 2, "-" & Mid(tStr, 3, 7), "")
Set RegEx = Nothing
End Sub[/vba] Again, I could do something similar for the dollar amount if you really wanted/need it. If you want to see it, let me know if there could be decimal points in the number, which would complicate it further.
Matt

[mvidas]

And just for fun..[vba]Private Sub txtAnnualValue_Change()
Dim RegEx As Object, tStr As String, nStr As String, nMod As Long
Set RegEx = CreateObject("vbscript.regexp")
RegEx.Global = True
RegEx.Pattern = "\D"
tStr = RegEx.Replace(txtAnnualValue.Text, "")
If Len(tStr) > 0 Then
nMod = Len(tStr) Mod 3
nStr = "$" & Left(tStr, nMod)
tStr = Mid(tStr, nMod + 1)
Do Until Len(tStr) = 0
nStr = nStr & IIf(Len(nStr) > 1, ",", "") & Left(tStr, 3)
tStr = Mid(tStr, 4)
Loop
End If
txtAnnualValue.Text = nStr
Set RegEx = Nothing
End Sub[/vba]This assumes no decimals..
Matt

[mvidas]

More just for fun, allows up to 2 decimal places now (not required)[vba]Private Sub txtAnnualValue_Change()
Dim RegEx As Object, nMod As Long
Dim tStr As String, nStr As String, dStr As String
Set RegEx = CreateObject("vbscript.regexp")
RegEx.Global = True
RegEx.Pattern = "[^0-9\.]"
nStr = RegEx.Replace(txtAnnualValue.Text, "")
RegEx.Pattern = "\.\d{0,2}"
If RegEx.Test(nStr) Then
With RegEx.Execute(nStr).Item(0)
dStr = .Value
nStr = Left(nStr, .FirstIndex)
End With
End If
RegEx.Pattern = "\D"
tStr = RegEx.Replace(nStr, "")
If Len(tStr) > 0 Then
nMod = Len(tStr) Mod 3
nStr = "$" & Left(tStr, nMod)
tStr = Mid(tStr, nMod + 1)
Do Until Len(tStr) = 0
nStr = nStr & IIf(Len(nStr) > 1, ",", "") & Left(tStr, 3)
tStr = Mid(tStr, 4)
Loop
End If
txtAnnualValue.Text = nStr & dStr
Set RegEx = Nothing
End Sub[/vba]

[ironj32]

thanks mvidas! this is exactly what i'm looking for.

[mdmackillop]

Show off!
Now I see where you got your title!

[mvidas]

I do tend to go a bit overboard sometimes

I tried making a custom function that would work for all 3 (passing it things like "00-0000000", etc) but it proved to be very difficult. Still playing around though, it could be rather useful in certain circumstances

[mvidas]

Hi Mvidas,
A while back ago you helped me with formating some numbers and it's awesome. However I would like to change it a little. I want to remove the "-" and I would like it to need to have exactly 9 numbers. I know how to remove the "-" and set the maximum length to 9: txtTaxID.Text = Left(tStr, 9), but I am having trouble setting the Minimum to 9. I would really appreciate your help! Thanks.

[vba]
Private Sub txtTaxID_Change()
Dim RegEx As Object, tStr As String
Set RegEx = CreateObject("vbscript.regexp")
RegEx.Global = True
RegEx.Pattern = "\D"
tStr = RegEx.Replace(txtTaxID.Text, "")
txtTaxID.Text = Left(tStr, 2) & IIf(Len(tStr) > 2, "-" & Mid(tStr, 3, 7), "")
Set RegEx = Nothing
End Sub
[/vba]

What do you want to happen if less than 9 characters are entered?
Say a user entered "123456"...

Would you want it "000123456"?[vba]txtTaxID.Text = Format(Left(tStr, 9), "000000000")[/vba]


Or would you want it "123456000"?[vba]txtTaxID.Text = Left(tStr & "000000000", 9)[/vba]

[Bob Phillips]

I do tend to go a bit overboard sometimes

I tried making a custom function that would work for all 3 (passing it things like "00-0000000", etc) but it proved to be very difficult. Still playing around though, it could be rather useful in certain circumstances

I've been writing a textbox mask facility for the past 5 years, and it is still as big a pile of doodoo as it was when I started

[mvidas]

I guess I don't feel so bad then, having only been a month and a half for me Not much further than I was before, I don't think I'll be working on it anymore (since if you can't get it, especially after 5 years, theres no hope for me)