Hi everybody,
I have a workbook that requires a user name and password to be able to
open the workbook, I do this to keep track of who has opened and done
any modifications in the workbook, the problem I’m having is when the
dialoge box pops up prompting for a “username and password”, if you do
not enter anything and just click the “enter password“ button this
error pops up.
Code:
--------------------
Run time error ’1004’:
Unable to get the vlookup property of the worksheet class.
--------------------
If I click the debug button it highlights this
Code:
--------------------
If password = WorksheetFunction.VLookup(UserList.Value, Range("Users_List"), 2, 0) Then
--------------------
When I click the end button, a user is still able to open files and
modify the info in the workbook. It also unhides my protected sheets
that have the passwords in them and the userlogs containing the list of
names and times accessed. Can someone please look at my codes below and
tell me what the problem is?
Code below is in “This workbook”
Code:
--------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("omi").Visible = xlSheetVisible
End Sub
Private Sub Workbook_Open()
If Sheets("omi").Visible < xlSheetVisible Then
Sheets("protected").Visible = xlSheetVisible
End If
Sheets("omi").Select
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim uName As String
uName = Environ("username")
Call FillLog(uName)
' MAKE SHEETS VISIBLE
Sheets("Protected").Visible = xlSheetVisible
Sheets("Userlog").Visible = xlSheetVisible
USERFORM11.Show
' Run modified date on front page
Application.Run ModDateFn
End Sub
--------------------
Code below is in the “Userform”
Code:
--------------------
Private Sub Cancel_Click()
Sheets("Protected").Visible = xlVeryHidden
MsgBox "Password Not Entered. Workbook will close!"
Unload Me
ThisWorkbook.Close
End Sub
Private Sub EnterPassword_Click()
Dim password As String
password = PasswordInput.Text
If password = WorksheetFunction.VLookup(UserList.Value, Range("Users_List"), 2, 0) Then
Sheets("protected").Visible = xlVeryHidden
Sheets("userlog").Visible = xlVeryHidden
Sheets("omi").Visible = xlVeryHidden
Sheets("wages").Visible = xlVeryHidden
MsgBox "Password Accepted"
Unload Me
Else
Sheets("protected").Visible = xlVeryHidden
Sheets("userlog").Visible = xlVeryHidden
Sheets("omi").Visible = xlVeryHidden
Sheets("wages").Visible = xlVeryHidden
MsgBox "Password Not Accepted, Workbook will close"
Unload Me
ThisWorkbook.Close
End If
' make sure USERLOG is active
Sheets("USERLOG").Activate
' determine the next empty row
NEXTROW = _
Application.WorksheetFunction.CountA(Range("c:c")) + 1
' Transfer the date
Cells(NEXTROW, 3) = UserList.Text
End Sub
Private Sub PasswordInput_Change()
End Sub
Private Sub UpdatePassword_Click()
Dim password As String
password = PasswordInput.Text
If password = WorksheetFunction.VLookup(UserList.Value, Range("Users_List"), 2, 0) Then
User = UserList.Value
Sheets("OMI").Range("A1").Value = User
Unload Me
UserForm22.Show
Else
MsgBox "Passwords do not match, please try again"
End If
End Sub
Private Sub UserForm_Terminate()
Dim password As String
password = PasswordInput.Text
If PasswordInput.Text = "" Or UserList.Value = "" Then
MsgBox "must use this form, workbook will close"
ThisWorkbook.Close
ElseIf password = WorksheetFunction.VLookup(UserList.Value, Range("Users_List"), 2, 0) Then
End If
End Sub
Private Sub UserList_Change()
End Sub
--------------------
I’m trying to make it where the user has to enter a password and
username, if the user tries to bypass by just clicking the “enter
password” button I want the workbook to close just like if it were a
wrong password.
I’ve tried everything and still cannot get the workbook to close if the
“enter password” button is the only thing clicked.
Any help would be greatly appreciated.
--
keithl816
------------------------------------------------------------------------
keithl816's Profile:
http://www.excelforum.com/member.php...o&userid=21287
View this thread:
http://www.excelforum.com/showthread...hreadid=385903