Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Up date a value in a text box
I have a short validation routine and makes sure input into a text box is a number. It works fine except, part of the code checks to see if a second decimal (period) was entered. If the test is true it will delete the second period. The code that does that is below: TestNumberLength = Len(TestNumber) - 1 If TestNumberLength < 0 Then TestNumberLength = 0 tb1.Value = Left(TestNumber, TestNumberLength) How do I get the displayed value in tb1 to show the new value. Presently it only changes when I make the next entry into the box. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Up date a value in a text box
I would test the KeyPress event and check that if a decimal period entered and cancel it if so. -- __________________________________ HTH Bob "JB" wrote in message ... I have a short validation routine and makes sure input into a text box is a number. It works fine except, part of the code checks to see if a second decimal (period) was entered. If the test is true it will delete the second period. The code that does that is below: TestNumberLength = Len(TestNumber) - 1 If TestNumberLength < 0 Then TestNumberLength = 0 tb1.Value = Left(TestNumber, TestNumberLength) How do I get the displayed value in tb1 to show the new value. Presently it only changes when I make the next entry into the box. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Up date a value in a text box
You don't say where this validation code is being used at (in an event as the user enters the "number" or after the user has completed his/her entry). Here is a past posting of mine that handles verification *after* the user has completed his/her entry... As for your question about checking numbers, here are two functions that I have posted in the past for similar questions..... one is for digits only and the other is for "regular" numbers: Function IsDigitsOnly(Value As String) As Boolean IsDigitsOnly = Len(Value) 0 And _ Not Value Like "*[!0-9]*" End Function Function IsNumber(ByVal Value As String) As Boolean ' Leave the next statement out if you don't ' want to provide for plus/minus signs If Value Like "[+-]*" Then Value = Mid$(Value, 2) IsNumber = Not Value Like "*[!0-9.]*" And _ Not Value Like "*.*.*" And _ Len(Value) 0 And Value < "." And _ Value < vbNullString End Function Here are revisions to the above functions that deal with the local settings for decimal points (and thousand's separators) that are different than used in the US (this code works in the US too, of course). Function IsNumber(ByVal Value As String) As Boolean Dim DP As String ' Get local setting for decimal point DP = Format$(0, ".") ' Leave the next statement out if you don't ' want to provide for plus/minus signs If Value Like "[+-]*" Then Value = Mid$(Value, 2) IsNumber = Not Value Like "*[!0-9" & DP & "]*" And _ Not Value Like "*" & DP & "*" & DP & "*" And _ Len(Value) 0 And Value < DP And _ Value < vbNullString End Function I'm not as concerned by the rejection of entries that include one or more thousand's separators, but we can handle this if we don't insist on the thousand's separator being located in the correct positions (in other words, we'll allow the user to include them for their own purposes... we'll just tolerate their presence). Function IsNumber(ByVal Value As String) As Boolean Dim DP As String Dim TS As String ' Get local setting for decimal point DP = Format$(0, ".") ' Get local setting for thousand's separator ' and eliminate them. Remove the next two lines ' if you don't want your users being able to ' type in the thousands separator at all. TS = Mid$(Format$(1000, "#,###"), 2, 1) Value = Replace$(Value, TS, "") ' Leave the next statement out if you don't ' want to provide for plus/minus signs If Value Like "[+-]*" Then Value = Mid$(Value, 2) IsNumber = Not Value Like "*[!0-9" & DP & "]*" And _ Not Value Like "*" & DP & "*" & DP & "*" And _ Len(Value) 0 And Value < DP And _ Value < vbNullString End Function -- Rick (MVP - Excel) "JB" wrote in message ... I have a short validation routine and makes sure input into a text box is a number. It works fine except, part of the code checks to see if a second decimal (period) was entered. If the test is true it will delete the second period. The code that does that is below: TestNumberLength = Len(TestNumber) - 1 If TestNumberLength < 0 Then TestNumberLength = 0 tb1.Value = Left(TestNumber, TestNumberLength) How do I get the displayed value in tb1 to show the new value. Presently it only changes when I make the next entry into the box. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Up date a value in a text box
Thanks all for a quick response here is all of the code for a text box called tb1. Private Sub tb1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Select Case KeyAscii Case 8, 13 Case 46 If KeyAscii = 45 Then If Len(Trim(tb1.Text)) 1 Then Beep KeyAscii = 0 End If End If Case 48 To 57 Case Else Beep KeyAscii = 0 End Select TestNumber = tb1.Value On Error GoTo failtest TestNumber = TestNumber * 1 'If TestNumber has two decimals it will fail this test. Exit Sub failtest: TestNumberLength = Len(TestNumber) - 1 If TestNumberLength < 0 Then TestNumberLength = 0 tb1.Value = Left(TestNumber, TestNumberLength) End Sub "Bob Phillips" wrote: I would test the KeyPress event and check that if a decimal period entered and cancel it if so. -- __________________________________ HTH Bob "JB" wrote in message ... I have a short validation routine and makes sure input into a text box is a number. It works fine except, part of the code checks to see if a second decimal (period) was entered. If the test is true it will delete the second period. The code that does that is below: TestNumberLength = Len(TestNumber) - 1 If TestNumberLength < 0 Then TestNumberLength = 0 tb1.Value = Left(TestNumber, TestNumberLength) How do I get the displayed value in tb1 to show the new value. Presently it only changes when I make the next entry into the box. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Up date a value in a text box
Okay, I see from your code that you are using a TextBox on a UserForm. I think you will be interested in the code in the section marked "For typing floating point numbers in the TextBox". The code parses the users entry as he/she types it; and it will permit only valid numerical entries (digits and one decimal point) to be typed or pasted into the TextBox (that last one is something most people forget to account for). You will have to change *all* my references to TextBox1 to the name you used for your TextBox (or, alternately, change your TextBox's name to TextBox1). Also, make sure you read the NOTES section at the end. Here is some code which I have posted in the past that is fully bullet-proof entry-wise (read the Notes section carefully though as it deals with some things you have to account for though). The following is general in nature; there is code below for both entries with digits only and for entries with decimal points... you will want to look at the second part of my posting which deals with floating point numbers. Also note that the code for floating point numbers requires you to specify a maximum number of digits that can be typed into both the integer part of the number as well as the decimal part of the number... if you don't want to place a limit on the user, just set each one to large value. The routines work quite well and protects the TextBox from pasting non-numeric entries (the user can paste valid data though) as well as stopping non-numeric keypresses. By the way, the code is set up for a TextBox with the default name of TextBox1; change those references to the name of your actual TextBox. Rick For typing digits only in the TextBox ===================================== Dim LastPosition As Long Private Sub TextBox1_Change() Static LastText As String Static SecondTime As Boolean If Not SecondTime Then With TextBox1 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 TextBox1_MouseDown(ByVal Button As Integer, _ ByVal Shift As Integer, _ ByVal X As Single, _ ByVal Y As Single) With TextBox1 LastPosition = .SelStart 'Place any other MouseDown event code here End With End Sub Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) With TextBox1 LastPosition = .SelStart 'Place any other KeyPress checking code here End With End Sub 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 TextBox1_Change() Static LastText As String Static SecondTime As Boolean Const MaxDecimal As Integer = 4 Const MaxWhole As Integer = 2 With TextBox1 If Not SecondTime Then If .Text Like "*[!0-9.]*" Or _ .Text Like "*.*.*" Or _ .Text Like "*." & String$(1 + MaxDecimal, "#") Or _ .Text Like String$(MaxWhole, "#") & "[!.]*" Then Beep SecondTime = True .Text = LastText .SelStart = LastPosition Else LastText = .Text End If End If End With SecondTime = False End Sub Private Sub TextBox1_MouseDown(ByVal Button As Integer, _ ByVal Shift As Integer, _ ByVal X As Single, _ ByVal Y As Single) With TextBox1 LastPosition = .SelStart 'Place any other MouseDown event code here End With End Sub Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) With TextBox1 LastPosition = .SelStart 'Place any other KeyPress checking code here End With End Sub NOTES ========================================= Note that you will have check for the Text property containing a single character consisting of a decimal point since that must be allowed as a starting character. If you want to allow negative, as well as positive values, then use this If statement in place of the second If statement in the Text1_Change event code above: If .Text Like "*[!0-9.+-]*" Or _ .Text Like "*.*.*" Or _ .Text Like "*." & String$(1 + MaxDecimal, "#") Or _ .Text Like "*" & String$(MaxWhole, "#") & "[!.]*" Or _ .Text Like "?*[+-]*" Then Note that now you will have to check the Text property for this one to see if it contains a single plus sign, minus sign or decimal point by themselves (that is, test if it is a one-character entry consisting of either a plus sign, minus sign or decimal point). I guess I should mention that I'm in the US where the decimal point is a "dot". If your decimal point is some other characters, then make the obvious substitutions in the If-Then tests above; or you could query the system for the decimal point character, store it in a variable and concatenate that into the string values above in place of the decimal point ("dot") that I show above. In keeping with the non-APIness of this solution, here is what I use to get the system's decimal point. DecimalPointSymbol = Format$(0, ".") -- Rick (MVP - Excel) "JB" wrote in message ... Thanks all for a quick response here is all of the code for a text box called tb1. Private Sub tb1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Select Case KeyAscii Case 8, 13 Case 46 If KeyAscii = 45 Then If Len(Trim(tb1.Text)) 1 Then Beep KeyAscii = 0 End If End If Case 48 To 57 Case Else Beep KeyAscii = 0 End Select TestNumber = tb1.Value On Error GoTo failtest TestNumber = TestNumber * 1 'If TestNumber has two decimals it will fail this test. Exit Sub failtest: TestNumberLength = Len(TestNumber) - 1 If TestNumberLength < 0 Then TestNumberLength = 0 tb1.Value = Left(TestNumber, TestNumberLength) End Sub "Bob Phillips" wrote: I would test the KeyPress event and check that if a decimal period entered and cancel it if so. -- __________________________________ HTH Bob "JB" wrote in message ... I have a short validation routine and makes sure input into a text box is a number. It works fine except, part of the code checks to see if a second decimal (period) was entered. If the test is true it will delete the second period. The code that does that is below: TestNumberLength = Len(TestNumber) - 1 If TestNumberLength < 0 Then TestNumberLength = 0 tb1.Value = Left(TestNumber, TestNumberLength) How do I get the displayed value in tb1 to show the new value. Presently it only changes when I make the next entry into the box. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date format to Text Date for use in Mail Merge | Excel Discussion (Misc queries) | |||
Formula concatenating date & text converts date to number | Excel Programming | |||
how do i convert text to date (mm/yy text to mm/dd/yyyy date)? | Excel Discussion (Misc queries) | |||
VBA convert day and date from text string to Excel date | Excel Programming | |||
Concatenating a Text and a Date without losing orginal Date Format | Excel Discussion (Misc queries) |