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 = “”
    
    Do
        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?