VBA Input Box in Excel for a Range in a worksheet...

[KP_SoCal]

With my code below you can see I set up a very non-secure means of executing certain commands based on the user names that are inputted.

As you can see, it's not very dynamic at all. I'd like to modify it to pull from a range of cells A2:A20 in worksheet "UserNames".

Any suggestions on how to tweak the code below to accommodate this would be greatly appreciated. Thanks again so much!

Public Sub Test()

Dim myValue

Dim myIDa As String
Dim myIDb As String
Dim myIDc As String
Dim myIDd As String

myIDa = "Fred" 
myIDb = "Bill" 
myIDc = "Jane" 
myIDd = "Abe"

myValue = InputBox("Input Authorized Name: ", "Authorization Confirmaton", )

If UCase(myValue) = myIDa Or UCase(myValue) = myIDb Or UCase(myValue) = myIDc Or UCase(myValue) = myIDd Then

   MsgBox "Authorized"

ElseIf myValue = "" Then
    Exit Sub

Else
    MsgBox "Unauthorized"

End If

End Sub

 

[RedBeard]

Why not iterate through the range and compare the input?

 

[L. Howard]

With my code below you can see I set up a very non-secure means of executing certain commands based on the user names that are inputted.

As you can see, it's not very dynamic at all. I'd like to modify it to pull from a range of cells A2:A20 in worksheet "UserNames".

Any suggestions on how to tweak the code below to accommodate this would be greatly appreciated. Thanks again so much!

Try this in a standard module.

Your list of names is here... ("Sheet5").Range("A2:A6") and you will adjust to suit your workbook, as well as the MsgBox texting advice to say what you want.

Howard

Option Explicit

Sub Test_Name()
    On Error Resume Next
    Dim aName As String

    aName = Application.inputbox(Prompt:="Please enter a name:", Title:="A Name of your choice", Type:=2)

    If aName <> "" Then
    
        If OnList(aName) Then
            MsgBox "Your input was " & aName
        
          Else
          
            MsgBox "            " & aName & vbCr & vbCr & _
                   "       Is not included" & vbCr & _
                   "in the authorization list!"
        End If
        
    End If
End Sub
Function OnList(sTxt As String) As Boolean
    On Error Resume Next
    Dim aList As Variant
    aList = ThisWorkbook.Worksheets("Sheet5").Range("A2:A6").Value
    OnList = Not IsError(Application.WorksheetFunction.Match(Trim(sTxt), aList, 0))
End Function

 

[KP_SoCal]

Terrific!!!!! Thank you!!!!