Options List for Inputbox

[vanhunk]

How can one provide a "data validation, list" functionality for an InputBox?

I want to limit the options in an InputBox to only the following values 1,2,3,4,5.

Regards,
vanhunk

[GTO]

I would think it much easier to use a textbox on a userform and probably the keypress and change events.

[mancubus]

afaik, you can't.

but you can specify the type using Application.Inputbox:
https://msdn.microsoft.com/en-us/lib.../ff839468.aspx

you should test the inputbox result to see if it meets the condition.
if not, alert user by displaying related messages...

an example from http://www.ozgrid.com/forum/showthread.php?t=54032 post#5

Sub GetText()

    Dim strReply As String 
     
    strReply = Application.InputBox(Prompt:="Enter Text", Type:=2) 
    If strReply = "False" Then Exit Sub 
     
    If Len(strReply) > 5 Or Len(strReply) < 4 Then 
        MsgBox "Text must be 5 characters long" 
        Run "GetText" 
    ElseIf IsNumeric(Left(strReply, 1)) Then 
        MsgBox "First character must be a letter" 
        Run "GetText" 
    ElseIf Not IsNumeric(Mid(strReply, 2, 4)) Then 
        MsgBox "last characters must be numeric" 
        Run "GetText" 
    End If

End Sub

[snb]

Sub M_snb()
  Do
    y = InputBox("number 1,2,3,4 or 5", "snb")
  Loop Until y > 0 And y < 6
End Sub

[vanhunk]

Thank you @GTO, @mancubus, and @snb:

I found the code below and adapted it for my application, but I very much prefers snb's concise and simple approach.

Sub Test()
    'Const var As String = "var"
    Dim projects As Variant
    Dim x As Long
    'projects = Array("x", "var", "y")
    projects = Array("1", "2", "3")
    On Error Resume Next
    x = WorksheetFunction.Match(4, projects, False)
    If Err = 0 Then
'       **** do something ****
        MsgBox "Found"
    Else
'       **** clear error ****
        Err.Clear
        MsgBox "Not Found"
    End If
    On Error GoTo 0
End Sub

Best Regards,
vanhunk