PDA

View Full Version : [SOLVED] Options List for Inputbox



vanhunk
02-20-2015, 03:09 AM
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
02-20-2015, 03:16 AM
I would think it much easier to use a textbox on a userform and probably the keypress and change events.

mancubus
02-20-2015, 03:22 AM
afaik, you can't.

but you can specify the type using Application.Inputbox:
https://msdn.microsoft.com/en-us/library/office/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
02-20-2015, 04:01 AM
Sub M_snb()
Do
y = InputBox("number 1,2,3,4 or 5", "snb")
Loop Until y > 0 And y < 6
End Sub

vanhunk
02-20-2015, 06:49 AM
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