PDA

View Full Version : Sleeper: Ask for a password before filtering



Sir Babydum GBE
03-14-2005, 04:22 AM
Hi

I have a tricky problem regarding filters and passwords.

What I would like to do is to create a hidden list of users and passwords (I can do this). Then, when the spreadsheet is opened an input box will ask for the user?s name and password and check it against my list. If the password is wrong it will throw them out of the workbook.

If the password is correct, when they attempt to use a filter on Column C, the only name the will be allowed to choose is their own.

I do not know how to use input boxes, nor how to check the entry against my hidden list, nor how to force the filter to show only one name. So any help on any of these aspects would be much appreciated.

Steiner
03-14-2005, 07:36 AM
Why do you want to use the autofilter then? Maybe it would be easier to have a button: show all / filter, where you apply the filter in VBA providing the name the user entered (and you hopefully have saved at that time).

Ok, this one is a basic example, just to see whether it goes into the right direction.

Loginname: user1
Password: pass1

MWE
03-14-2005, 07:38 AM
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

Sir Babydum GBE
03-22-2005, 04:11 AM
Both,

Firstly, sorry for the long delay in replying - your time and help is very much appreciated - After posting this, I was called to work on another project temporarily, and could not give your replies the attention they deserved.

Secondly, regarding why I want to use AutoFilters along with protection. A new spreadsheet is being created that is designed as a repository for loads of info, from which pivot tables will then extract the data needed to provide extensive management information on success, productivity, time keeping, targets etc.

Much of the information is not confidential, but some of it is*. So, when the spreadsheet is opened, I wanted to filter out the information pertaining to all except the user.

As to why all this is being done on one sheet - I have no choice. That is what I've been asked to look into.

Anyhow, I'm going to look at your suggestions in greater detail now. Thanks again

*I say "confidential", but I mean that other users would prefer not to have their info on general display.