Inputbox Validation

Here’s a little goodie I find myself re-writing every once in a while.
It’s a simple “Here is a question, give me a valid answer” type routine.

Note the use of the infinite Do .. Loop.
Just make sure your exits are covered (Cancel button)

Sub test()
    Dim str As String, bln As Boolean
    ‘Set up a default value
   str = “Blue”
        str = InputBox(“Favourite Colour?”, “Colour?”, str)
        ‘check for Cancel
       If str = “” Then
            ‘Cancelled – exit from do loop with failure
           bln = False
            Exit Do
            ‘Check the entry against valid answers
           Select Case LCase(str)
                Case “black”, “red”, “green”, “yellow”, “blue”, “magenta”, “cyan”, “black”
                    ‘Match found – exit from do loop with success
                   bln = True
                    Exit Do
                Case Else
                    ‘oops – not a valid answer
                   MsgBox “Not a recognised colour”, vbExclamation, “Error”
            End Select
        End If
    Loop    ‘infinite loop

    If bln Then
        MsgBox “Your favourite colour is “ & str
    End If
End Sub
Posted in Uncategorized

10 thoughts on “Inputbox Validation

  1. what happens if you want to receive a positive number?

    can you use a variant as the input variable and cast it into an integer

  2. I notice that you’re checking for cancel by testing whether the inputbox returns a zero length string. What if a zero length string is legitimate input? It turns out that if the user cancels, the string is null rather than zero length, so we can test for that.

    Dim strInput As String
    strInput = InputBox(“do something”)
    If Len(strInput) = 0 Then
    If StrPtr(strInput) = 0 Then
    Debug.Print “The user clicked Cancel”
    Debug.Print “The user clicked Enter, but typed nothing”
    End If
    End If


  3. Robert,

    That’s an excellent suggestion and certainly worth using.

    Read about StrPtr.

    To use in the posted code:
    Change: If str = “” Then
    To: If StrPtr(str) = 0 Then


  4. Hello, ive just read this, and i was wondering how you would validate an answer so that if it contained certain characters, then an error message is produced?

    for example, i want dates to be entered, but there are so many ways of entering a date, i only want the one way, which is 06.09.2006, i dont want 06/09/2006 or, as many people have been doing it 06_09_2006.

    I just want it to check for /’s and _’s and come up saying, incorrect format.

  5. I am trying to create a performance model. I am using vb were we input values. I want it so that when i close the vb screen the excel sheets also should close

  6. How can I validate that a whole, positive number is entered into an InputBox? No decimals, no letters, no blanks…else it displays an msgbox error and loops back to the beginning.

    I keep getting an error.


  7. This is an answer to Paul’s question, but he probably will not see it because of the time that has passed since he asked it; however, I think others reading this thread in the future will find this answer interesting. Paul wanted to know how to insure the date inputted by the user was in 06.09.2006 format and not in any other date format. Personally, I can’t think of a good reason for wanting this as Paul could always take a valid date and format it anyway he wants afterwards; however, to answer his question…

    If DateIn Like “##.##.####” Then
    MsgBox “Thank you… that is the correct format”
    MsgBox “Incorrect format”
    End If

    The Like operator can provide for some very powerful testing and is worth adding to your programming arsenol. I would note, though, while the above does guarantee the format is correct, it does not test to see if the inputted numbers make up a valid date… that would have to be a separate test.

  8. @Ryan

    Here is a function that should help you out as it determines whether the value passed into it is made up of only digits or not…

    Function IsDigitsOnly(Value As String) As Boolean
    IsDigitsOnly = Len(Value) > 0 And Not Value Like “*[!0-9]*”
    End Function

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.