Validating an InputBox

The InputBox method of the Application object can be used to prompt the user for, well, input. To make sure the input is valid, I use a Do..Loop to force the user to enter valid data or press “Cancel”.

When the user clicks Cancel, the InputBox method returns False. The variable that holds the input is dimmed as a Variant. This will avoid type mismatch errors when it’s tested for both False and a number. Also, if the user enters invalid data, I use the vDefault variable to repopulate the input box. Here’s an example:

Sub GetValidData()
    Dim vInput As Variant
    Dim sPrompt As String
    Dim sTitle As String
    Dim vDefault As Variant
    sPrompt = “Enter a number 1 through 10?
    sTitle = “Number Entry”
    vDefault = “”
        vInput = Application.InputBox(sPrompt, sTitle, vDefault, , , , , 1)
        vDefault = CStr(vInput)
    Loop Until (vInput >= 1 And vInput <= 10) Or vInput = False
    If Not vInput = False Then
        MsgBox vInput
    End If
End Sub

Note that the user can enter False into the textbox with the same effect as clicking Cancel. I don’t know how to avoid that. Anyone?

Posted in Uncategorized

One thought on “Validating an InputBox

  1. This seems to do it:

            vInput = Application.InputBox(sPrompt, sTitle, vDefault, , , , , 1)
            If TypeName(vInput) = “Boolean” Then Exit Do
        Loop Until (vInput >= 1 And vInput <= 10)

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

Leave a Reply

Your email address will not be published.