I agree with Steiner that you probably do not need to use the AutoFilter in the way suggested.
Username/password checking is a pretty common need. The code below is an extract of a procedure I wrote/use. The full procedure is set up to check the combination of application filenames, users, passwords, last logon and preferences (most of which you do not need). The assumptions regarding the sheet where the usernames/passwords are stored and assumptions re processing are listed. I tested this stripped version and it seems to run OK
Sub MWE_CheckUser()
' UserName and Password sheet assumptions:
' 1. assumes sheet is in this workbook
' 2. assumes sheet is called "HK" (HouseKeeping)
' 3. assumes that row 1 is titles
' 4. assumes that usernames are in 1st col, passwords in 2nd col
'' Processing assumptions:
' 1. username is not case sensitive (everything converted to lower case)
' 2. usernames are unique
' 3. username for this appl can be obtained via two methods:
' 3.1 from user
' 3.2 is standard logon name and can be fetched from
' Application.UserName [not sure if this is in conflict with
' unique user name assumption]
' currently method is set to "logon name"
' 4. there is only one valid password for a given username
' 5. password IS case sensitive
' 6. by assigning same password to every user, appl is limited to
' known users who know general password
Dim I As Integer, LastRow As Integer
Dim PassWord As String, PassWordKept As String, UserName As String
Dim UserNameFetchMethod As String, xlHK As String
xlHK = "HK"
UserNameFetchMethod = "logon name"
' UserNameFetchMethod = "user enters"
Step1: ' make sure this user is on list
Select Case UserNameFetchMethod
Case "user enters"
UserName = LCase(InputBox("enter username"))
Case "logon name"
UserName = LCase(Application.UserName)
End Select
LastRow = MWE_FindLastRow(xlHK)
For I = 2 To LastRow
If LCase(Worksheets(xlHK).Cells(I, 1)) = UserName Then
' UserName found; fetch stored password for later compare
PassWordKept = Worksheets(xlHK).Cells(I, 2)
GoTo Step2
End If
Next I
Select Case UserNameFetchMethod
Case "user enters"
Rtn = MsgBox("username entered is not valid. Try again?", _
vbYesNo + vbQuestion)
If Rtn = vbYes Then GoTo Step1
Application.Quit
Exit Sub
Case "logon name"
MsgBox "Sorry, you are not a recognized user of this application.", _
vbCritical, "Hello " + UserName
Application.Quit
Exit Sub
End Select
Step2: ' user is on list, prompt for password
PassWord = InputBox("enter password ", "Hello " + UserName)
' check password
If PassWord <> PassWordKept Then
Rtn = MsgBox("password entered is not valid. Try again?", _
vbYesNo + vbQuestion)
If Rtn = vbYes Then GoTo Step2
Application.Quit
Exit Sub
End If
' username is valid and password matches; do other stuff
End Sub
Function MWE_FindLastRow(WorkSheetName) As Long
With Worksheets(WorkSheetName)
MWE_FindLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByRows, xlPrevious).Row
End With
End Function
Reply With Quote
