validating input box values

[lloyd33]

I would like to force users to enter only numbers in an input box and display an error message if users enter characters is this possible using the input box, if not what is the best way to achieve this.

 

[brianb]

We have to bear in mind that an Inputbox always gives text. We have to test if the text can be interpreted as a number, and convert to a number if the situation requires it. We may need to test further to see if the number is in the correct format for our purposes eg. date.

Here is some code that uses 2 methods. Can use one or the other or both.
Isnumeric tests if the text looks like a number. Declaring MyNumber as double ensures that the text will be converted, and will give a trappable error if it cannot.

Sub test()
    Dim MyNumber As Double
    '-------------------
    rsp = InputBox("Please enter a number")
    If rsp = "" Then Exit Sub
    On Error GoTo NotNumber
    If IsNumeric(rsp) Then
        '- next line gives error if not number
        MyNumber = rsp
        ' do something
    Else
        MsgBox ("Entry is not numeric")
    End If
    Exit Sub
NotNumber:
    MsgBox ("Entry is not numeric")
End Sub

 

[lloyd33]

thanks

thanks a lot Brian i will try it out.

 

[Mile-O]

You may find it simpler to just create a small popup form with a textbox and two buttons.

On the textbox's Keypress, you'd only need:

If Not IsNumeric(Chr(KeyAscii)) Then KeyAscii = 0

Then, your cancel button closes the form, and your Confirm button checks that the textbox ahs data and then passes the value back to the calling form.