I am getting duplicate decimal points on numeric validation

A

Allan

Is there extra code to prevent duplicate decimal points on numeric validation?
This only happens if the first 2 characters are both periods or minus signs.


Private Sub Intrate_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Select Case KeyAscii

Case 8 To 10, 13, 27 'Control characters
Case 45, 46 ' negative and period


If KeyAscii = 45 Then ' hypen/negative
If Len(Trim(Intrate.Text)) > 1 Then
Beep
KeyAscii = 0
End If
End If
If KeyAscii = 45 Then ' hypen/negative
If Len(Trim(Intrate.Text)) > 1 Then
Beep
KeyAscii = 0
End If
End If

Case 48 To 57 'numbers
Case Else 'Discard anything else
Beep
KeyAscii = 0
End Select

End Sub


Thanks
 
J

Jim Rech

You have this line twice:

If KeyAscii = 45 Then

rather than doing 46.

--
Jim
| Is there extra code to prevent duplicate decimal points on numeric
validation?
| This only happens if the first 2 characters are both periods or minus
signs.
|
|
| Private Sub Intrate_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
|
| Select Case KeyAscii
|
| Case 8 To 10, 13, 27 'Control characters
| Case 45, 46 ' negative and period
|
|
| If KeyAscii = 45 Then ' hypen/negative
| If Len(Trim(Intrate.Text)) > 1 Then
| Beep
| KeyAscii = 0
| End If
| End If
| If KeyAscii = 45 Then ' hypen/negative
| If Len(Trim(Intrate.Text)) > 1 Then
| Beep
| KeyAscii = 0
| End If
| End If
|
| Case 48 To 57 'numbers
| Case Else 'Discard anything else
| Beep
| KeyAscii = 0
| End Select
|
| End Sub
|
|
| Thanks
| --
| AH
 
R

Rick Rothstein \(MVP - VB\)

Is there extra code to prevent duplicate decimal points on numeric
validation?
This only happens if the first 2 characters are both periods or minus
signs.
Click to expand...

You will have other problems with your code... it will allow the user to
paste in non-numeric text from the Clipboard. Below is a routine that I
originally developed for the compiled VB world; but, with minor
modifications, works in Excel. I'm assuming your TextBox (named Intrate) is
located on a UserForm. Copy/Paste the code that follows my signature into
the UserForm's code window. Note that you can individually specify how many
digits coupled with an optional plus/minus sign can be typed in before a
decimal point and how many digits can be typed in after the decimal point
(see the comment block at the beginning of the code). The routine allows
only one leading plus or minus sign (optional) and only one decimal point to
be typed in; it limits the number of digits as described above; and it will
not allow any other characters to be typed or pasted in.

Rick

'For typing floating point numbers in the TextBox
'=========================================
' Set the maximum number of digits before the
' decimal point in the MaxWhole constant. Set
' the maximum number of digits after the decimal
' point in the MaxDecimal constant.
Dim LastPosition As Long

Private Sub Intrate_Change()
Static LastText As String
Static SecondTime As Boolean
Const MaxDecimal As Integer = 2
Const MaxWhole As Integer = 5
With Intrate
If Not SecondTime Then
If .Text Like "*[!0-9.+-]*" Or _
.Text Like "*.*.*" Or _
.Text Like "*." & String$(1 + MaxDecimal, "#") Or _
.Text Like "*" & String$(MaxWhole, "#") & "[!.]" Or _
.Text Like "?*[+-]*" Then
Beep
SecondTime = True
.Text = LastText
.SelStart = LastPosition
Else
LastText = .Text
End If
End If
End With
SecondTime = False
End Sub

Private Sub Intrate_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, _
ByVal X As Single, _
ByVal Y As Single)
With Intrate
LastPosition = .SelStart
'Place any other MouseDown event code here
End With
End Sub

Private Sub Intrate_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
With Intrate
LastPosition = .SelStart
'Place any other KeyPress checking code here
End With
End Sub
 
A

Allan

Thanks for the code I used it in part with keypress

Private Sub Intrate_Change()

Dim LastPosition As Long
Static LastText As String
Const MaxDecimal As Integer = 2
Const MaxWhole As Integer = 4

With Intrate

If .Text Like "*.." Or _
.Text Like "*--" Or _
.Text Like "*." & String$(1 + MaxDecimal, "#") Or _
.Text Like "*." & String$(1 + MaxDecimal, "#") Or _
.Text Like "*" & String$(MaxWhole, "#") & "[!.]" Then

Beep
.SelStart = LastPosition
.Text = LastText
Else
LastText = .Text
End If
End With
....
End Sub

Private Sub Intrate_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii

Case 8 To 10, 13, 27 'Control characters
Case 46 ' period
If KeyAscii = 46 Then ' period
If Len(Trim(Intrate.Text)) > 2 Then
Beep
KeyAscii = 0
End If
End If
Case 48 To 57 'numbers
Case Else 'Discard anything else
Beep
KeyAscii = 0
End Select

With Intrate
LastPosition = .SelStart
'Place any other KeyPress checking code here
End With
End Sub
 
A

Allan

Thanks For the code I used in in part with the keypress action

Private Sub Intrate_Change()

Dim LastPosition As Long
Static LastText As String
Const MaxDecimal As Integer = 2
Const MaxWhole As Integer = 4

With Intrate

If .Text Like "*.." Or _
.Text Like "*--" Or _
.Text Like "*." & String$(1 + MaxDecimal, "#") Or _
.Text Like "*." & String$(1 + MaxDecimal, "#") Or _
.Text Like "*" & String$(MaxWhole, "#") & "[!.]" Then

Beep
.SelStart = LastPosition
.Text = LastText
Else
LastText = .Text
End If
End With
....

End Sub

Private Sub Intrate_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii

Case 8 To 10, 13, 27 'Control characters
Case 46 ' period
If KeyAscii = 46 Then ' period
If Len(Trim(Intrate.Text)) > 2 Then
Beep
KeyAscii = 0
End If
End If
Case 48 To 57 'numbers
Case Else 'Discard anything else
Beep
KeyAscii = 0
End Select

With Intrate
LastPosition = .SelStart
'Place any other KeyPress checking code here
End With
End Sub
 
R

Rick Rothstein \(MVP - VB\)

I'm curious... why didn't you use all the code I posted exactly as is? I ask
because your code still allows a user to Paste in data other than digits or
a decimal point. You also removed the ability to type in a minus sign. I
will tell you that the code "package" I posted has been thoroughly tested
and it works well and exactly as advertised. Why don't you comment out your
code, copy/paste mine into your code window and try it out... especially try
pasting in a bad entry (both into your code and into mine).

Rick


Allan said:
Thanks For the code I used in in part with the keypress action

Private Sub Intrate_Change()

Dim LastPosition As Long
Static LastText As String
Const MaxDecimal As Integer = 2
Const MaxWhole As Integer = 4

With Intrate

If .Text Like "*.." Or _
.Text Like "*--" Or _
.Text Like "*." & String$(1 + MaxDecimal, "#") Or _
.Text Like "*." & String$(1 + MaxDecimal, "#") Or _
.Text Like "*" & String$(MaxWhole, "#") & "[!.]" Then

Beep
.SelStart = LastPosition
.Text = LastText
Else
LastText = .Text
End If
End With
...

End Sub

Private Sub Intrate_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii

Case 8 To 10, 13, 27 'Control characters
Case 46 ' period
If KeyAscii = 46 Then ' period
If Len(Trim(Intrate.Text)) > 2 Then
Beep
KeyAscii = 0
End If
End If
Case 48 To 57 'numbers
Case Else 'Discard anything else
Beep
KeyAscii = 0
End Select

With Intrate
LastPosition = .SelStart
'Place any other KeyPress checking code here
End With
End Sub



--
AH


Allan said:
Is there extra code to prevent duplicate decimal points on numeric
validation?
This only happens if the first 2 characters are both periods or minus
signs.


Private Sub Intrate_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Select Case KeyAscii

Case 8 To 10, 13, 27 'Control characters
Case 45, 46 ' negative and period


If KeyAscii = 45 Then ' hypen/negative
If Len(Trim(Intrate.Text)) > 1 Then
Beep
KeyAscii = 0
End If
End If
If KeyAscii = 45 Then ' hypen/negative
If Len(Trim(Intrate.Text)) > 1 Then
Beep
KeyAscii = 0
End If
End If

Case 48 To 57 'numbers
Case Else 'Discard anything else
Beep
KeyAscii = 0
End Select

End Sub


Thanks
Click to expand...
Click to expand...
 
R

Rick Rothstein \(MVP - VB\)

Why don't you comment out your code,

By the way, if you are not set up to be able to do this, here is how. In the
VBA editor, right-click on a blank area of the Toolbar and select Customize
from the popup menu that appears. Click the Commands tab on the dialog box
that comes up, click on Edit in the Categories list on the left and find
Comment Block in the Commands list on the right. Click-drag the Comment
Block line to a blank area of the Toolbar and release the mouse button. This
will place an icon on the Toolbar that you can use to comment out a large
block of text (simply highlight the code lines in code window and click the
icon). While you have the Customize dialog open, also click-drag the
Uncomment Block entry to the Toolbar... you can use it to remove comment
blocking (simply highlight the lines making up the comment block and click
the icon).

Rick
 
A

Allan

Thanks Rick for the comment buttons very helpful.
I tried your code in a new userform and it works great.
For my purpose I didn't need "+- signs" for interest rate.

A couple of things happpening to me anyway,

My mouse isn't active so no way I could paste text in, but that doesn't mean
in it not possible if i have a form where a mouse is active.

I also noted that if I entered 99999.99 or 55.55 and then tried to enter
another number i got a beep as it should, the cursor moves to the left most
of the entered numbers and i am able to enter more numbers.
eg.
99999.99 'beep cursor moves to left
666666666699999.99 ' can add more number to the left of original

I commented out the line .SelStart = LastPosition this
this helped by not having the cursor move left but still allows numbers to
be entered to the left of the original set of numbers.

Again thanks


AH
 
R

Rick Rothstein \(MVP - VB\)

Thanks Rick for the comment buttons very helpful.
I tried your code in a new userform and it works great.
For my purpose I didn't need "+- signs" for interest rate.

I also noted that if I entered 99999.99 or 55.55 and then tried to enter
another number i got a beep as it should, the cursor moves to the left
most
of the entered numbers and i am able to enter more numbers.
eg.
99999.99 'beep cursor moves to left
666666666699999.99 ' can add more number to the left of original
Click to expand...

I remember fixing that bug once a long time ago. Apparently, I grabbed a
copy of an older version of that routine which was missing an asterisk in
one of the Like comparisons. The corrected code is below. Sorry about any
confusion that might have caused you.

If you want to forbid the plus sign from being typed in (I had figured it
wouldn't matter to any calculations you did down the line if the user
happened to enter leading plus sign, so I left it in), simply delete the 2
occurrences of it inside the long If-Then statement with all the Like
comparisons in the Intrate_Change event procedure (but don't delete anything
else). By the way, the LastPosition line that you deleted is needed... it
allows the user to move the cursor and, if he/she hasn't exceeded the limits
set for the section, continue typing in at the new location. The code below
should work fine now exactly as is.

Rick


Option Explicit

'For typing floating point numbers in the TextBox
'=========================================
' Set the maximum number of digits before the
' decimal point in the MaxWhole constant. Set
' the maximum number of digits after the decimal
' point in the MaxDecimal constant.
Dim LastPosition As Long

Private Sub Intrate_Change()
Static LastText As String
Static SecondTime As Boolean
Const MaxDecimal As Integer = 2
Const MaxWhole As Integer = 5
With Intrate
If Not SecondTime Then
If .Text Like "*[!0-9.+-]*" Or _
.Text Like "*.*.*" Or _
.Text Like "*." & String$(1 + MaxDecimal, "#") Or _
.Text Like "*" & String$(MaxWhole, "#") & "[!.]*" Or _
.Text Like "?*[+-]*" Then
Beep
SecondTime = True
.Text = LastText
.SelStart = LastPosition
Else
LastText = .Text
End If
End If
End With
SecondTime = False
End Sub

Private Sub Intrate_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, _
ByVal X As Single, _
ByVal Y As Single)
With Intrate
LastPosition = .SelStart
'Place any other MouseDown event code here
End With
End Sub

Private Sub Intrate_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
With Intrate
LastPosition = .SelStart
'Place any other KeyPress checking code here
End With
End Sub
 
A

Allan

Hey, That did the trick !!
Works like a charm.
Exactly what I was looking for.
This is going to become my basic routine for number validation.


--
AH


Rick Rothstein (MVP - VB) said:
Thanks Rick for the comment buttons very helpful.
I tried your code in a new userform and it works great.
For my purpose I didn't need "+- signs" for interest rate.

I also noted that if I entered 99999.99 or 55.55 and then tried to enter
another number i got a beep as it should, the cursor moves to the left
most
of the entered numbers and i am able to enter more numbers.
eg.
99999.99 'beep cursor moves to left
666666666699999.99 ' can add more number to the left of original
Click to expand...

I remember fixing that bug once a long time ago. Apparently, I grabbed a
copy of an older version of that routine which was missing an asterisk in
one of the Like comparisons. The corrected code is below. Sorry about any
confusion that might have caused you.

If you want to forbid the plus sign from being typed in (I had figured it
wouldn't matter to any calculations you did down the line if the user
happened to enter leading plus sign, so I left it in), simply delete the 2
occurrences of it inside the long If-Then statement with all the Like
comparisons in the Intrate_Change event procedure (but don't delete anything
else). By the way, the LastPosition line that you deleted is needed... it
allows the user to move the cursor and, if he/she hasn't exceeded the limits
set for the section, continue typing in at the new location. The code below
should work fine now exactly as is.

Rick


Option Explicit

'For typing floating point numbers in the TextBox
'=========================================
' Set the maximum number of digits before the
' decimal point in the MaxWhole constant. Set
' the maximum number of digits after the decimal
' point in the MaxDecimal constant.
Dim LastPosition As Long

Private Sub Intrate_Change()
Static LastText As String
Static SecondTime As Boolean
Const MaxDecimal As Integer = 2
Const MaxWhole As Integer = 5
With Intrate
If Not SecondTime Then
If .Text Like "*[!0-9.+-]*" Or _
.Text Like "*.*.*" Or _
.Text Like "*." & String$(1 + MaxDecimal, "#") Or _
.Text Like "*" & String$(MaxWhole, "#") & "[!.]*" Or _
.Text Like "?*[+-]*" Then
Beep
SecondTime = True
.Text = LastText
.SelStart = LastPosition
Else
LastText = .Text
End If
End If
End With
SecondTime = False
End Sub

Private Sub Intrate_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, _
ByVal X As Single, _
ByVal Y As Single)
With Intrate
LastPosition = .SelStart
'Place any other MouseDown event code here
End With
End Sub

Private Sub Intrate_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
With Intrate
LastPosition = .SelStart
'Place any other KeyPress checking code here
End With
End Sub
Click to expand...
 
R

Rick Rothstein \(MVP - VB\)

I'm glad you liked it. Since you are planning on using it for your basic
number validation routine, I thought you might like its companion routine
for proofing digits only. I have included the code for it after my
signature. There are no limits to set within the code for this version as
the TextBox itself has a MaxLength property where you can limit the total
number of characters typed in. I used the same TextBox name (Intrate) in
this routine as I did for the floating point one... the TextBox names are
all specified in With statements, so changing them to match your current
TextBox name will be easy enough to do. Oh, if you want to allow your user
to be able to type in a leading plus or minus sign, change this If-Then
statement (in the Change event procedure)...

If .Text Like "*[!0-9]*" Then

to this instead...

If .Text Like "*[!0-9+-]*" Or .Text Like "?*[+-]*" Then

Rick


'For typing digits only in the TextBox
'=====================================
Dim LastPosition As Long

Private Sub Intrate_Change()
Static LastText As String
Static SecondTime As Boolean
If Not SecondTime Then
With Intrate
If .Text Like "*[!0-9]*" Then
Beep
SecondTime = True
.Text = LastText
.SelStart = LastPosition
Else
LastText = .Text
End If
End With
End If
SecondTime = False
End Sub

Private Sub Intrate_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, _
ByVal X As Single, _
ByVal Y As Single)
With Intrate
LastPosition = .SelStart
'Place any other MouseDown event code here
End With
End Sub

Private Sub Intrate_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
With Intrate
LastPosition = .SelStart
'Place any other KeyPress checking code here
End With
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) 1
change event help 2
Reference Label from TextBox 0
Require user to enter numeric value in TextBox 4
Calling a sub 1
Format Text & Numbers 5
ascii validation question 1
Help with code restricting TextBox entry 6

Top