Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I am getting duplicate decimal points on numeric validation
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I am getting duplicate decimal points on numeric validation
You have this line twice:
If KeyAscii = 45 Then rather than doing 46. -- Jim "Allan" wrote in message ... | 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I am getting duplicate decimal points on numeric validation
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. 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
I am getting duplicate decimal points on numeric validation
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 -- AH "Allan" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
I am getting duplicate decimal points on numeric validation
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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
I am getting duplicate decimal points on numeric validation
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" wrote in message ... 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" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
I am getting duplicate decimal points on numeric validation
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
I am getting duplicate decimal points on numeric validation
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 "Rick Rothstein (MVP - VB)" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
I am getting duplicate decimal points on numeric validation
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 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
I am getting duplicate decimal points on numeric validation
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)" wrote: 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 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
I am getting duplicate decimal points on numeric validation
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Decimal Points | New Users to Excel | |||
How can I convert decimal commas to decimal points? | Excel Discussion (Misc queries) | |||
Aligning Decimal Points with non-numeric data | Excel Discussion (Misc queries) | |||
Decimal Points | Excel Worksheet Functions | |||
Decimal points not needed | Excel Worksheet Functions |