Run-time Error : Unable to get Vlookup property of the WorksheetFunction Class

I'm trying to create a login userform on vba but it returns me the error stated in the title. So here's some background.

I have a sheet called "User_List" which has Username and their associated password with them . The range starts from Range B3 to C1000. Column B has all the usernames while Column C has the passwords. I would like to create a login userform such that after a user inputs the username and their password, vba will search user list and determine if the details are correct. Once that's confirmed, vba will direct the user to another sheet called home page. Below is my code.

Private Sub MLIB_Click()
Dim UserName As String
Dim PassWord As String

' MUN is the name of the textbox associated to the Username
' MPW is the name of the textbox associated to the Password
UserName = MUN.Value
PassWord = MPW.Value

If UserName = Application.WorksheetFunction.VLookup(UserName, Sheets("User_List").Range("B3:C1000"), 1, True) Then
    If PassWord = Application.WorksheetFunction.VLookup(PassWord, Sheets("User_List").Range("B3:C1000"), 2, True) Then
       Sheets("Home_Page").Activate
       Unload Me
    End If
End If
MsgBox "Sorry, Incorrect Login Details"
End Sub

Been trying to figure this out but it's taking ages! Appreciate any help!

Solution

You should be using False as the optional [range_lookup] parameter of the WorksheetFunction object VLOOKUP function for an exact match on unsorted data.

First, check to see if the user name provided exists in column B with a quick MATCH function. If it exists, check to see if the associated password in column C is the same as the one provided.

With Worksheets("User_List")
    If Not IsError(Application.Match(UserName, .Columns(2), 0)) Then
        If Password = Application.VLookup(UserName, .Range("B:C"), 2, False) Then
           Worksheets("Home_Page").Activate
           Unload Me
        End If
    End If
End With
MsgBox "Sorry, Incorrect Login Details"

VBA is (by default) case sensitive so while the user name lookup is not case-sensitive, the password check is case sensitive. This is likely what you want.