Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox Decimile Problem
Hi,
I have a UserForm with many textboxes. the below Procedure is for the Unit Price Textbox. I put two restrictions on it, 1. to restrict blank entry 2. to restrict alphabatical entry both are working fine, but when I try to enter the value like 23.05 or 135.15 its not accepted. I mean the value in decimile its not accepting. Private Sub TxtUnitPrice_Exit(ByVal Cancel As MSForms.ReturnBoolean) ' to restrict blank entry If txtUnitPrice.Text = "" Then MsgBox "Sorry, please enter the Unit Price to proceed..." Cancel = True End If End Sub Private Sub txtUnitPrice_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) ' to restrict entry for Numbers only If KeyAscii < Asc("0") Or KeyAscii Asc("9") Then Interaction.Beep KeyAscii = 0 End If End Sub if anyone knows how to fix this problem. pls send me the solution, I will be highly appreciated...... Thanks and regards. Syed Shahzad Zafar Madinah |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox Decimile Problem
Maybe
Private Sub txtUnitPrice_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Select Case KeyAscii Case 8, 9, 27, 45, 46, 48 To 57 Case Else Beep KeyAscii = 0 End Select End Sub Mike "Shazi" wrote: Hi, I have a UserForm with many textboxes. the below Procedure is for the Unit Price Textbox. I put two restrictions on it, 1. to restrict blank entry 2. to restrict alphabatical entry both are working fine, but when I try to enter the value like 23.05 or 135.15 its not accepted. I mean the value in decimile its not accepting. Private Sub TxtUnitPrice_Exit(ByVal Cancel As MSForms.ReturnBoolean) ' to restrict blank entry If txtUnitPrice.Text = "" Then MsgBox "Sorry, please enter the Unit Price to proceed..." Cancel = True End If End Sub Private Sub txtUnitPrice_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) ' to restrict entry for Numbers only If KeyAscii < Asc("0") Or KeyAscii Asc("9") Then Interaction.Beep KeyAscii = 0 End If End Sub if anyone knows how to fix this problem. pls send me the solution, I will be highly appreciated...... Thanks and regards. Syed Shahzad Zafar Madinah |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox Decimile Problem
hi
does the textbox accept the 23 part of the number and not the .05 or does it not accept at all. i'm guessing. but the keypress sub is returning a integer which is a not-decimal value. i think your problem has something to do with that. i would suggest that you change your declaration to a single or double. perhaps using CDbl() instead of ASC() might help also becasue ASC() returns an integer also. i have not tested. guessing based on memory. regards FSt1 "Shazi" wrote: Hi, I have a UserForm with many textboxes. the below Procedure is for the Unit Price Textbox. I put two restrictions on it, 1. to restrict blank entry 2. to restrict alphabatical entry both are working fine, but when I try to enter the value like 23.05 or 135.15 its not accepted. I mean the value in decimile its not accepting. Private Sub TxtUnitPrice_Exit(ByVal Cancel As MSForms.ReturnBoolean) ' to restrict blank entry If txtUnitPrice.Text = "" Then MsgBox "Sorry, please enter the Unit Price to proceed..." Cancel = True End If End Sub Private Sub txtUnitPrice_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) ' to restrict entry for Numbers only If KeyAscii < Asc("0") Or KeyAscii Asc("9") Then Interaction.Beep KeyAscii = 0 End If End Sub if anyone knows how to fix this problem. pls send me the solution, I will be highly appreciated...... Thanks and regards. Syed Shahzad Zafar Madinah |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox Decimile Problem
Hi,
Forgot to add, that's not bullet proof. I allowed ASCII 45 (-) in case you want negative values allowed but the user could enter 123-45. You could check for this seperately or don't allow - by deleting 45 from select case. Mike "Mike H" wrote: Maybe Private Sub txtUnitPrice_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Select Case KeyAscii Case 8, 9, 27, 45, 46, 48 To 57 Case Else Beep KeyAscii = 0 End Select End Sub Mike "Shazi" wrote: Hi, I have a UserForm with many textboxes. the below Procedure is for the Unit Price Textbox. I put two restrictions on it, 1. to restrict blank entry 2. to restrict alphabatical entry both are working fine, but when I try to enter the value like 23.05 or 135.15 its not accepted. I mean the value in decimile its not accepting. Private Sub TxtUnitPrice_Exit(ByVal Cancel As MSForms.ReturnBoolean) ' to restrict blank entry If txtUnitPrice.Text = "" Then MsgBox "Sorry, please enter the Unit Price to proceed..." Cancel = True End If End Sub Private Sub txtUnitPrice_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) ' to restrict entry for Numbers only If KeyAscii < Asc("0") Or KeyAscii Asc("9") Then Interaction.Beep KeyAscii = 0 End If End Sub if anyone knows how to fix this problem. pls send me the solution, I will be highly appreciated...... Thanks and regards. Syed Shahzad Zafar Madinah |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox Decimile Problem
On Jul 13, 2:55*pm, Mike H wrote:
Maybe Private Sub txtUnitPrice_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Select Case KeyAscii * Case 8, 9, 27, 45, 46, 48 To 57 * * Case Else * * * * Beep * * * * KeyAscii = 0 End Select End Sub Mike "Shazi" wrote: Hi, I have a UserForm with many textboxes. the below Procedure is for the Unit Price Textbox. I put two restrictions on it, 1. * to restrict blank entry 2. * to restrict alphabatical entry both are working fine, but when I try to enter the value like 23.05 or *135.15 its not accepted. I mean *the value in decimile its not accepting. Private Sub TxtUnitPrice_Exit(ByVal Cancel As MSForms.ReturnBoolean) ' to restrict blank entry * * * * If txtUnitPrice.Text = "" Then * * * * MsgBox "Sorry, please enter the Unit Price to proceed...." * * * * Cancel = True * * * * End If End Sub Private Sub txtUnitPrice_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) ' * to restrict entry for Numbers only * * If KeyAscii < Asc("0") Or KeyAscii Asc("9") Then * * * * Interaction.Beep * * * * KeyAscii = 0 * * End If End Sub if anyone knows how to fix this problem. pls send me the solution, I will be highly appreciated...... Thanks and regards. Syed Shahzad Zafar Madinah- Hide quoted text - - Show quoted text - Thank you Mr. Mike, you solved my problem. your code is working perfectly. Once again thank you very much for your support. Regards. Shahzad |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox Decimile Problem
Glad I could help
"Shazi" wrote: On Jul 13, 2:55 pm, Mike H wrote: Maybe Private Sub txtUnitPrice_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Select Case KeyAscii Case 8, 9, 27, 45, 46, 48 To 57 Case Else Beep KeyAscii = 0 End Select End Sub Mike "Shazi" wrote: Hi, I have a UserForm with many textboxes. the below Procedure is for the Unit Price Textbox. I put two restrictions on it, 1. to restrict blank entry 2. to restrict alphabatical entry both are working fine, but when I try to enter the value like 23.05 or 135.15 its not accepted. I mean the value in decimile its not accepting. Private Sub TxtUnitPrice_Exit(ByVal Cancel As MSForms.ReturnBoolean) ' to restrict blank entry If txtUnitPrice.Text = "" Then MsgBox "Sorry, please enter the Unit Price to proceed...." Cancel = True End If End Sub Private Sub txtUnitPrice_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) ' to restrict entry for Numbers only If KeyAscii < Asc("0") Or KeyAscii Asc("9") Then Interaction.Beep KeyAscii = 0 End If End Sub if anyone knows how to fix this problem. pls send me the solution, I will be highly appreciated...... Thanks and regards. Syed Shahzad Zafar Madinah- Hide quoted text - - Show quoted text - Thank you Mr. Mike, you solved my problem. your code is working perfectly. Once again thank you very much for your support. Regards. Shahzad |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox Decimile Problem
Thank you Mr. Mike, you solved my problem. your code
is working perfectly. You are aware that the routine Mike posted is not bullet-proof from several different angles. Mike pointed out the problem of the misplaced minus sign. On top of that, the routine allows multiple minus signs and multiple decimal points to be typed in, and in any combination. Finally, your users will be able to paste in any bad (non-numeric) data they want (you cannot stop that using only a KeyPress event). 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, ".") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
textbox problem | Excel Programming | |||
Textbox problem | Excel Programming | |||
Textbox Problem | Excel Programming | |||
Textbox problem | Excel Programming | |||
TextBox SetFocus Problem | Excel Programming |