M
Monomeeth
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
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