Validate entry of an input box

Question

Im trying to get an input box to validate the entries a user will make.

i'm using the below script but cant get the validation to work, any help would be appreciated.

 Sub inputbox()

 Dim Manager As Long

     On Error Resume Next

        Application.DisplayAlerts = False

        Manager = Application.inputbox(Prompt:="Please enter a manager.", Title:="Pick A Manager Name",      Type:=1)

On Error GoTo 0

Application.DisplayAlerts = True

    If Manager = "" Then

        Exit Sub

    ElseIf Manager <> Ben, Cameron, Chris, Martin, Peter Then

    MsgBox "Incorrect Name, pick a new one!"

    Else

        MsgBox "Your input was " & Manager

    End If

 End Sub

PatricK · Accepted Answer · 2014-02-25T22:34:38.040

Although a Sub name same as built in ones are not recommended, you can do what you are after like below.

First you need to change the InputBox Type to 2 (String), since you are comparing with String. Then you should make a function to check if the input is part of a Manager List.

Sub inputbox()
    On Error Resume Next
    Dim Manager As String

    Manager = Application.inputbox(Prompt:="Please enter a manager name:", Title:="Pick A Manager Name", Type:=2)

    If Manager <> "" Then
        If IsManager(Manager) Then
            MsgBox "Your input was " & Manager
        Else
            MsgBox "Incorrect Name, pick a new one!"
        End If
    End If
End Sub

Private Function IsManager(sTxt As String) As Boolean
    Dim aManagers As Variant, oItem As Variant, bAns As Boolean
    aManagers = Array("Ben", "Cameron", "Chris", "Martin", "Peter")
    bAns = False
    For Each oItem In aManagers
        If LCase(oItem) = LCase(Trim(sTxt)) Then
            bAns = True
            Exit For
        End If
    Next
    IsManager = bAns
End Function

UPDATE (Improved version suggested by Simon1979):

Private Function IsManager(sTxt As String) As Boolean
    On Error Resume Next
    Dim aManagers As Variant
    aManagers = Array("Ben", "Cameron", "Chris", "Martin", "Peter")
    IsManager = Not IsError(Application.WorksheetFunction.Match(Trim(sTxt), aManagers, 0))
End Function

I like the use of array, I should have caught that one. Rather than looping through the array you could also use `If IsError(Application.Match(Trim(sTxt), aManagers, False)) Then`, returns true if the entry was NOT in the array. — Simon1979, Feb 25 '14 at 22:07
Thanks for the tip! To other readers: it should be `Application.WorksheetFunction.Match()` — PatricK, Feb 25 '14 at 22:29

score 1 · Answer 2 · answered Feb 24 '14 at 01:07

Haven't used the InputBox with Excel but I imagine it will be very similar to the Access one. I use the below method to validate inputbox:

Dim strM as string

EnterManager:
strM = InputBox("Enter Manager.")
If StrPtr(strM) = 0 Then 'Cancel was pressed
    ' Handle what to do if cancel pressed
    Exit Sub
ElseIf Len(strM) = 0 Then 'OK was pressed with nothing entered
    MsgBox "You must enter a Manager."
    GoTo EnterBuyer
End If

To add your criteria you could add on another If, I'm not sure you can use the approach you have for checking the list of names. Also don't understand how you compare a long Manager with a list of names Ben, Cameron, Chris, Martin, Peter, unless they are assigned variables, in which case I would suggest adding prefixes so it is more obvious such as lBen as opposed to strBen so you can easily see the difference in variable type.

If strM <> "Ben" And strM <> "Cameron" And strM <> "Chris" And strM <> _
    "Martin" And strM <> "Peter" Then
    MsgBox "Incorrect Name, pick a new one!"
Else
    MsgBox "Your input was " & strM
End If

OK looks good, how do i then have the value of strM entered into sheets called "PID" in cell "G9". thanks. — Adrian Gornall, Feb 24 '14 at 01:32

Validate entry of an input box

2 Answers2