Hello

I have been trying to use a macro to allow users to open their own
personalised logs which are password protected. Users click on a button and
then the macro asks for their User ID. After entering their User ID the macro
then asks for their password. If the password is correct, the macro then
opens that user's individual file.

For this to work, the macro reads data from a hidden worksheet. This hidden
worksheet has data in three columns: A, B and C. The first column contains
the User ID, the second column contains passwords, and the third column
contains the full file path.

The problem I have is this: The hidden worksheet has details for 64 users,
but the macro only recognises 14 of these users. As a result, these users
cannot open their files. I am at a total loss to explain why. It makes no
difference what order the users are listed within the hidden worksheet, and
there is no obvious pattern to either those users who are recognised or those
who aren't. I really am at a total loss.

Any help would be greatly appreciated as I need this for work on Monday.

Thanks.


The macro code is below:

Sub FindStaff()
Dim FoundCell As Range
Dim ws1 As Worksheet
Dim Search As Variant
Dim Passwrd As Variant
Dim MyFile As String
Dim MyTitle As String
Dim OpenWB As Workbook

Set ws1 = Worksheets("Users") '<< change as required

MyTitle = "Open My WorkBook"

startsearch:
Search = Application.InputBox(prompt:="User ID",
Title:=MyTitle, Type:=2)


If Search = False Then Exit Sub

'search for staff number
Set FoundCell = ws1.Columns("A").Find _
(Search, LookIn:=xlValues, _
LookAt:=xlWhole)

If FoundCell Is Nothing = False Then

i = 1
enterpassword:
Passwrd = Application.InputBox(prompt:="Enter Password" & Chr(10) &
"Attempt " & i, Title:=MyTitle2, Type:=2)

If Passwrd = False Then Exit Sub

'check password value in Col B
If FoundCell.Offset(0, 1).Value = CStr(Passwrd) Then

'get file name & path from Col C
MyFile = FoundCell.Offset(0, 2).Value

On Error GoTo myerror
Set OpenWB = Workbooks.Open(MyFile, Password:=Passwrd)

'do stuff here

Else

msg = MsgBox("Password Not Valid", vbInformation, MyTitle)

i = i + 1

If i 3 Then

Exit Sub

Else

GoTo enterpassword

End If

End If

Else

msg = MsgBox("Value " & Search & " Not Found", vbInformation, MyTitle)

GoTo startsearch

End If

myerror:
If Err 0 Then

MsgBox (Error(Err))
Err.Clear

End If

End Sub