Fixing Non numeric entry to numeric field

[mleblanc001]

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]

Have you looked at Data Validation?

[mdmackillop]

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]

Or this code by Tom Urtis

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]

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]

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]

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]

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