CODEWITHSUNDEEP
vbaexcel
Oday Salim
Oday Salim

Reputation: 1147

Validate user input value from list

I am very new to VBA and I would very much appreciate support in this.

I am writing a simple code that asks a user to input a 'password'. The list of passwords are hard-coded in VBA. If the user inputs a password that matches what was hard-coded in VBA, then it will put it on cell 'B2', if it is wrong, then the user will get an error message that the password was wrong.

My code so far is:

Sub Password()
    Dim Password As String
    Dim Msg As String
    Dim List(Password1, Password2) As String
    Msg = "please enter password"
    Password = InputBox(Msg)
    If Password <> List Then
         MsgBox "Incorrect Password!"
    ActiveSheet.Range("B2").Value = Password
End Sub

I am not sure where I am going wrong?

Many thanks

Upvotes: 3

Views: 786

Answers (1)

gizlmo
gizlmo

Reputation: 1922

I would suggest using a Collection. This is how to create a collection and add items to it:

Dim pwList As New Collection
pwList.Add "TestPass1"
pwList.Add "TestPass2"

Unfortunately, the VBA Collection has no default Function for checking if a string is an element of it. This Function can be used to check if a collection contains a string (Just paste this Function somewhere in your code):

Public Function CollectionContains(col As Collection, val As String) As Boolean
Dim item As Variant

For Each item In col
    If item = val Then
        CollectionContains = True
        Exit Function
    End If
Next item

CollectionContains = False
End Function

Now, this is how your code should look like:

Sub Pw()
Dim Password As String
Dim pwList As New Collection
pwList.Add "TestPass1"
pwList.Add "TestPass2"

Password = InputBox("please enter password")
If CollectionContains(pwList, Password) Then
    ActiveSheet.Range("B2").Value = Password
Else
     MsgBox "Incorrect Password!"
End If
End Sub

Upvotes: 3

Related Questions