Only numeric input in a InputBox in word vba

[JasonEnsor]

Hi,

I have an InputBox that pops up in my document, however if somebody types text in it then it crashes my system. i want them to only input a number, if they input anything other than that i want a msgbox to pop up and tell them to only enter a numeric value.

I have 3 inputboxes in total so i would like a snippet of code that i could run for all 3 instances of this.

Any ideas?

 

[MajP]

Public Function verifyNumeric(vInput) As Boolean
  Dim strMsg As String
  If Not IsNumeric(vInput) Then
     strMsg = "Hey fool input numeric only. '" & vInput & "' aint no numeric value."
    MsgBox strMsg, vbCritical, "Enter Numeric"
  Else
    verifyNumeric = True
  End If
End Function

Public Sub TestNumeric()
  Dim vInput As Variant
  Do
    vInput = InputBox("Enter a numeric value", "Enter Numeric")
  Loop Until verifyNumeric(vInput)
End Sub

 

[Andrzejek]

MajP, nice work, but....

IsNumeric is not a 'full prove' (or is it 'fool prove'?) method and it will allow 123e4 or 12d3 as input and it will pass your code as correct. And that may not be what JasonEnsor needs.

You can find somewhere something like IsDigit or IsInteger (not a VBA functions, just some code somebody wrote).

But I would just use my own little Form with a text box and a code like this:

Private Sub txtSomeTextBox_KeyPress(KeyAscii As Integer)

If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 8 Then
    [green]'Only numbers are entered - it is OK['green]
Else
    KeyAscii = 0
    Beep
End If

End Sub

Have fun.

---- Andy

 

[SkipVought]

Foolproof.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

Public Function verifyNumeric(vInput As Variant) As Boolean
  Dim strMsg As String
  If Not myIsNumeric(vInput) Then
     strMsg = "Hey fool input numeric only. '" & vInput & "' aint no numeric value."
    MsgBox strMsg, vbCritical, "Enter Numeric"
  Else
    verifyNumeric = True
  End If
End Function

Public Sub TestNumeric()
  Dim vInput As Variant
  Do
    vInput = InputBox("Enter a numeric value", "Enter Numeric")
  Loop Until verifyNumeric(vInput)
End Sub

Public Function myIsNumeric(vInput As Variant) As Boolean
  Dim I As Integer
  Dim strChr As String
  If Not Trim(vInput & " ") = "" Then
    myIsNumeric = True
    vInput = CStr(vInput)
    For I = 1 To Len(vInput)
       strChr = Mid(vInput, I, 1)
       If Not IsNumeric(strChr) Then myIsNumeric = False
    Next I
  End If
End Function

Sorry a little lazy. But yes a user form is always preferred.

 

[fumei]

My objection is that there is no method to escape. Clicking Cancel returns the error message. Clicking the Close "X" also returns the error message. The only way out is to input a numeric value.


unknown

 

[MajP]

Public Sub TestNumeric()
  Dim vInput As Variant
  Do
    vInput = InputBox("Enter a numeric value", "Enter Numeric")
    If Trim(vInput & " ") = "" Then Exit Do
  Loop Until verifyNumeric(vInput)
End Sub

 

[PHV]

Why not replace this:
If Not myIsNumeric(vInput) Then
with simply this ?
If vInput Like "*[!0-9]*" Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

Better but not quite good enough.
That code fails on
-123
12.3
-123.3

How about?

Public Function myIsNumeric(vInput As Variant) As Boolean
  If Not Trim(vInput & " ") = "" Then
    vInput = Trim(vInput)
    myIsNumeric = (Not vInput Like "*[!0,1,2,3,4,5,6,7,8,9,.,-]*") And IsNumeric(vInput)
  End If
End Function

 

[MajP]

Based on the everyone's good suggestions, here is better version of how you can use a standard input box and validate the data.

The function is called "inputBoxVerified"

Public Function InputBoxVerified(prompt As String, Optional InputTitle As String = "", Optional verifyType As verType = vtNumber, Optional verifyPrompt As String = "", Optional verifyTitle As String = "") As Variant
  Do
    InputBoxVerified = InputBox(prompt, InputTitle)
    If Trim(InputBoxVerified & " ") = "" Then Exit Do
  Loop Until verifyInput(InputBoxVerified, verifyType, verifyPrompt, verifyTitle)
End Function

So you can specify the prompt, title of input box, verification message, verification message title, and the type of data to check
numeric
integer numeric
alphabetic only
date time
boolean
and you could add your own pretty simply.

an example call would be

Public Sub testInputBoxVerified()
  Dim vInput As Variant
  vInput = InputBoxVerified("Enter a DateValue value", "Enter date", vtDateTime, "Fix Input", "Invalid Input")
  MsgBox vInput, vbInformation
End Sub

Place all code in a standard module

Option Explicit

Public Enum verType
  vtNumber = 0
  vtIntegerNumber = 1
  vtBoolean = 2
  vtDateTime = 3
  vtAlphabeticOnly = 4
End Enum

Public Sub testInputBoxVerified()
  Dim vInput As Variant
  vInput = InputBoxVerified("Enter a DateValue value", "Enter date", vtDateTime, "Fix Input", "Invalid Input")
  MsgBox vInput, vbInformation
End Sub

Public Function InputBoxVerified(prompt As String, Optional InputTitle As String = "", Optional verifyType As verType = vtNumber, Optional verifyPrompt As String = "", Optional verifyTitle As String = "") As Variant
  Do
    InputBoxVerified = InputBox(prompt, InputTitle)
    If Trim(InputBoxVerified & " ") = "" Then Exit Do
  Loop Until verifyInput(InputBoxVerified, verifyType, verifyPrompt, verifyTitle)
End Function

Public Function verifyInput(vInput As Variant, verifyType As verType, verifyPrompt As String, verifyTitle As String) As Boolean
  Dim strPrompt As String
  Select Case verifyType
  Case vtNumber
    strPrompt = "Number Required." & vbCrLf
    verifyInput = myIsNumeric(vInput)
  Case vtIntegerNumber
    strPrompt = "Integer Number Required." & vbCrLf
    verifyInput = myIsIntegerNumeric(vInput)
  Case vtBoolean
    strPrompt = "Boolean Value Required." & vbCrLf
    verifyInput = myIsBoolean(vInput)
  Case vtDateTime
    strPrompt = "Date Required." & vbCrLf
    verifyInput = IsDate(vInput)
    If verifyInput Then vInput = CDate(vInput)
  Case vtAlphabeticOnly
    strPrompt = "Alphabetic Only Value Required." & vbCrLf
    verifyInput = myIsAlphabetic(vInput)
  Case Else
    verifyInput = True
  End Select
  strPrompt = strPrompt & "Invalid input value: " & vInput & vbCrLf & vbCrLf & verifyPrompt
  If Not verifyInput Then
      MsgBox strPrompt, vbInformation, verifyTitle
  End If
End Function

Public Function myIsNumeric(vInput As Variant) As Boolean
  If Not Trim(vInput & " ") = "" Then
    vInput = Trim(vInput)
    myIsNumeric = (Not vInput Like "*[!0,1,2,3,4,5,6,7,8,9,.,-]*") And IsNumeric(vInput)
  End If
End Function

Public Function myIsBoolean(vInput As Variant) As Boolean
  If Not Trim(vInput & " ") = "" Then
    vInput = Trim(vInput)
    myIsBoolean = (vInput = "Yes" Or vInput = "No" Or vInput = "True" Or vInput = "False" Or vInput = "-1" Or vInput = "0")
  End If
End Function

Public Function myIsIntegerNumeric(vInput As Variant) As Boolean
  If Not Trim(vInput & " ") = "" Then
    vInput = Trim(vInput)
    myIsIntegerNumeric = (Not vInput Like "*[!0,1,2,3,4,5,6,7,8,9,-]*") And IsNumeric(vInput)
  End If
End Function

Public Function myIsAlphabetic(vInput As Variant) As Boolean
  If Not Trim(vInput & " ") = "" Then
    vInput = Trim(vInput)
    myIsAlphabetic = Not vInput Like "*[!A-Z]*"
  End If
End Function

if you have the time a user form is normally a better approach, but this provides a quick reuseable solution.

 

[fumei]

Seems to me that using a test of ASCII values on a Change event on a userform is MUCH easier. Unless of course one is stuck on using an InputBox.


unknown

 

[MajP]

Fumei,
How do you figure MUCH easier? Now that the code is written you can build a verifying input box in seconds without building a form or any additional code. Also this was the OPs original question. Also I have already stated several times that a user form is the preferred solution. So lighten up.

 

[fumei]

I did not realize i was being heavy. My apologies, I did not realize I was being critical. Oh...I wasn't.


unknown