Format Text & Numbers

[ViViC]

Hi all,

I have a problem with userform textbox formatting. The end result of data
entry is to be shown as 01/01234 or BH/014567 the first two characters is a
claim year (can be 01 for 2001etc or BH for really old claim prefix) and the
last 5 numbers are a file number (always 5 numbers).

Presently the code below allows only numbers for all entries. Another problem
is If I enter 01234 it will display as 00/01234, it should display as
01/00234.

Code to date as follows

Private Sub txtInvClaim_AfterUpdate()
With Me.txtInvClaim
.Value = Format(.Value, "00/00000")
End With
End Sub

Private Sub txtInvClaim_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Dim OKChar As Boolean
OKChar = True
If Len(Me.txtInvClaim.Value) >= 7 Then
OKChar = False
Else
Select Case keyascii
Case Asc("0") To Asc("9")
'ok
Case Else
OKChar = False
End Select
End If
If OKChar = False Then
keyascii = 0
'Beep
End If
End Sub

Any help will be greatly appreciated and many thaxs to all

ViViC

 

[Peter T]

Not entirely sure how you want to do it but perhaps you can adapt the
following to your needs -

Private Sub TextBox1_Change()
Dim s As String
Static bExit As Boolean

If Not bExit Then
s = Me.TextBox1.Text
s = Replace(s, "/", "")

If Len(s) > 2 Then
s = Left$(s, 2) & "/" & Right$("00000" & Mid$(s, 3, Len(s) - 2), 5)
End If

If TextBox1.Text <> s Then
bExit = True
TextBox1.Text = s
End If
End If
bExit = False

End Sub

Regards,
Peter T

 

[ViViC via OfficeKB.com]

Peter your great, it worked perfectly and I add the a small cahnge to turn
the text characters into uppercase. The amount of time spent on this has been
huge. Now for some other small problems
Many thanks again

Vic

Not entirely sure how you want to do it but perhaps you can adapt the
following to your needs -

Private Sub TextBox1_Change()
Dim s As String
Static bExit As Boolean

If Not bExit Then
s = Me.TextBox1.Text
s = Replace(s, "/", "")

If Len(s) > 2 Then
s = Left$(s, 2) & "/" & Right$("00000" & Mid$(s, 3, Len(s) - 2), 5)
End If

If TextBox1.Text <> s Then
bExit = True
TextBox1.Text = s
End If
End If
bExit = False

End Sub

Regards,
Peter T

Hi all,

[quoted text clipped - 37 lines]

ViViC

 

[ViViC via OfficeKB.com]

Sorry Peter,
I was testing code when I found I could enter +, -, * in the first two
characters ie BH/ could show as +*/ should be Alpha or numeric. Maybe it
can't be done. Any help would be appreciated

ViViC

Peter your great, it worked perfectly and I add the a small cahnge to turn
the text characters into uppercase. The amount of time spent on this has been
huge. Now for some other small problems
Many thanks again

Vic

Not entirely sure how you want to do it but perhaps you can adapt the
following to your needs -

[quoted text clipped - 27 lines]

 

[Peter T]

Here's one approach -

Private Sub TextBox1_Change()
Dim i As Long
Dim s As String
Dim ba() As Byte ' byte-array
Static bExit As Boolean

If Not bExit Then
s = UCase(Me.TextBox1.Text)
's = Replace(s, "/", "") ' redundant

ba = s
s = ""
For i = 0 To UBound(ba) Step 2
If ba(i + 1) = 0 Then
Select Case ba(i)
Case 48 To 57
' digits 0-9
s = s & Chr(ba(i))
Case 65 To 90
''letters only in 1st 2 characters
If i <= 3 Then
s = s & Chr(ba(i))
End If
End Select
End If
Next

If Len(s) > 1 Then
s = Left$(s, 2) & "/" & Right$("00000" & Mid$(s, 3, Len(s) - 2), 5)
End If

If TextBox1.Text <> s Then
bExit = True
TextBox1.Text = s
End If
End If
bExit = False

End Sub

There's a simple one-liner to "input-mask" to ensure only letters or numbers
with use of RegExp (regular expressions), trouble is it would take me at
least an hour to re-learn the correct syntaxt! I find the byte array method
easy to understand and adapt. As written, user can only input letters or
numbers in first two places, but last five must be numbers only. FYI, if the
string is say "AB" the array will fill with values 65,0,66,0 and hence the
loop with step one with check the subsequent byte in the loop is zero.

I'd be tempted to change the Right$("00000" to "-----", or "?????" then in
the AfterUpdate event (or any routine that later extacts the user input)
replace - or ? with 0.

Regards,
Peter T

Sorry Peter,
I was testing code when I found I could enter +, -, * in the first two
characters ie BH/ could show as +*/ should be Alpha or numeric. Maybe it
can't be done. Any help would be appreciated

ViViC

Peter your great, it worked perfectly and I add the a small cahnge to turn
the text characters into uppercase. The amount of time spent on this has been
huge. Now for some other small problems
Many thanks again

Vic

Not entirely sure how you want to do it but perhaps you can adapt the
following to your needs -

[quoted text clipped - 27 lines]

ViViC

 

[ViViC via OfficeKB.com]

Hi Peter,

Works at treat, I have only used VBA for a short time and have always had
trouble understanding array's but I will have sometime up my sleeve in next
week or so. I have been involved in using Access for comparing old payroll
system to new payroll system results which is now live. Just thrown in the
deep end. This is not my normal work programming (Insurance Claims etc is), a
very fast learning curve which is great for me. This project is but a small
part of a very large govt project.

All I can say many thanks for your work & time, it is greatly appreciated

ViVic

Here's one approach -

Private Sub TextBox1_Change()
Dim i As Long
Dim s As String
Dim ba() As Byte ' byte-array
Static bExit As Boolean

If Not bExit Then
s = UCase(Me.TextBox1.Text)
's = Replace(s, "/", "") ' redundant

ba = s
s = ""
For i = 0 To UBound(ba) Step 2
If ba(i + 1) = 0 Then
Select Case ba(i)
Case 48 To 57
' digits 0-9
s = s & Chr(ba(i))
Case 65 To 90
''letters only in 1st 2 characters
If i <= 3 Then
s = s & Chr(ba(i))
End If
End Select
End If
Next

If Len(s) > 1 Then
s = Left$(s, 2) & "/" & Right$("00000" & Mid$(s, 3, Len(s) - 2), 5)
End If

If TextBox1.Text <> s Then
bExit = True
TextBox1.Text = s
End If
End If
bExit = False

End Sub

There's a simple one-liner to "input-mask" to ensure only letters or numbers
with use of RegExp (regular expressions), trouble is it would take me at
least an hour to re-learn the correct syntaxt! I find the byte array method
easy to understand and adapt. As written, user can only input letters or
numbers in first two places, but last five must be numbers only. FYI, if the
string is say "AB" the array will fill with values 65,0,66,0 and hence the
loop with step one with check the subsequent byte in the loop is zero.

I'd be tempted to change the Right$("00000" to "-----", or "?????" then in
the AfterUpdate event (or any routine that later extacts the user input)
replace - or ? with 0.

Regards,
Peter T

Sorry Peter,
I was testing code when I found I could enter +, -, * in the first two

[quoted text clipped - 15 lines]