PDA

View Full Version : Fixing Non numeric entry to numeric field



mleblanc001
05-29-2017, 02:56 PM
Hi VBA gurus, I have a form which requests a numeric value such as percentage, quantity and unit price. I would like to know how to force users to input a numeric value in these fields if they make a mistake by putting a non numeric entry such as "5 tambourines" in the quantity. I'm attaching an example of what I mean.

Thank you

mikerickson
05-29-2017, 03:17 PM
Have you looked at Data Validation?

mdmackillop
05-29-2017, 03:27 PM
Private Sub TextBox10_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsNumeric(TextBox10) Then
MsgBox "Please enter numeric value"
Cancel = True
TextBox10.Text = 0
End If
End Sub

mdmackillop
05-29-2017, 03:52 PM
Or this code by Tom Urtis (https://www.mrexcel.com/forum/excel-questions/59453-textboxes-setfocus-selstart-sellength.html#post277394)


Private Sub TextBox10_Change()
Dim strTxt As String
strTxt = TextBox10.Text
If Right(strTxt, 1) Like "[!0-9]" = True Then
Select Case Len(TextBox10.Text)
Case 0
Exit Sub
Case 1
Application.EnableEvents = False
TextBox10.Text = ""
Application.EnableEvents = True
MsgBox "Only numbers are allowed!", 48, "Get with the program!"
Case Else
Application.EnableEvents = False
TextBox5.Text = Left(strTxt, Len(strTxt) - 1)
Application.EnableEvents = True
End Select
End If
End Sub

SamT
05-29-2017, 03:56 PM
This is one way. May need refinement to fit
Dim TempValue As String


Private Sub TextBox10_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
TempValue = Me.TextBox10
End Sub


Private Sub TextBox10_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case Is < 32
Exit Sub
Case 48 To 57
Me.TextBox10 = TempValue & Chr(KeyCode)
Case Else
Me.TextBox10 = TempValue
'Show Numbers only warning
Me.TextBox10.SetFocus
End Select

TempValue = ""
End Sub

mleblanc001
05-30-2017, 10:54 AM
Hi Gurus,
All you suggestions are great, I used mdmackillop's suggestion as it was easier for me to apply it to my situation, however, the percentage cell was giving me percentages in the hundreds on the invoice (e.g. 5% was 500%). I was able to adjust that by adding the script below in "This Workbook". Also, I had to write a do loop to accomodate the input boxes. However, I am having an issue on the Report worksheet. It is also displaying the percentage in the hundreds. I have tried many ways to rectify this issue but to no avail. would anyone have a suggestion on this. I'm going to try to attach the latest workbook for your review and to help other users. If I can't attache the workbook, I'll include the code on another post. Thank you all for all your suggestions. It is greatly appreciated.


Dim C As Variant
For Each C In [D18]
If C.Value < 1 Then
C.Value = C.Value * 100
End If
C.NumberFormat = "0.00\%"
Next C

mdmackillop
05-30-2017, 03:20 PM
Revised file attached with some suggested changes
% corrected
Simplified textbox code by passing to another sub.
Msgbox instead of Inputbox
Combobox sample for Prepaid/Collect. See UserformActivate for adding data
Use combos where you have specific data e.g Sales Tax rates.
Personally I would use an "Add Item" button on the main form to transfer data and clear specific textboxes for the next item.

mleblanc001
05-30-2017, 05:45 PM
The changes you made were amazing, thank you for sharing. I made some slight changes to the textbox5 for the Sales tax rate. I couldn't put a decimal number on the userform, so I modified your private sub for textbox5 to allow for the inclusion of two decimal places. as shown below. I really liked your message box with the vbyesno. I knew about it but forgot to put it in. Thank you for all your help, I truly appreciate it. I'm attaching my final version for other users to enjoy.

Private Sub TextBox5_Change()
Call NumbersOnly(TextBox5)
TextBox5.Value = Format(TextBox5.Value, "#.##")
End Sub