Password Issue

K

keithl816

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.
 
A

Anne Troy

Please don't put password protection in the code. Anyone can open the
workbook and choose NOT to enable macros. If you want to FORCE users to
enable macros, try this KB entry at VBAX.
http://www.vbaexpress.com/kb/getarticle.php?kb_id=379

*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


keithl816 said:
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:
Click to expand...
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,
Click to expand...
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,
Click to expand...
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,
Click to expand...
Range("Users_List"), 2, 0) Then
 
K

keithl816

Hi Anne,

Thank you for replying, unfortunately I need to use this method t
monitor my workbook activity. I have a few associates that use thi
workbook and are not very good with excel. So I monitor who opens th
workbook and modifies it.

I've protected everything possible including all worksheets in th
workbook. So disabling the macros basically locks all buttons down t
where a user cannot promt a userform to appear. Likewise if a passwor
was incorrect it automatically closes the book.

I'm the main user, the only time anyone else uses it, is if I am o
vacation or away from my desk for a short period of time.

The reason I require a password to login is to monitor who enters data
what date, and what computer was used to open the workbook. It's only
select few.

The problem I'm having is when the dialoge box pops up prompting for
“username and password”, if you do not enter anything and just clic
the “enter password“ button an error pops up. If you choose to clic
the end button in the open dialogue box instead of debug it continue
without closing, exposing the password and userlog.

Everything works fine but this little issue. What can I add to my cod
to keep this error message from continuing.

Again, thanks for replying, any help is appreciated
 
J

JE McGimpsey

The way I do this is to disable the Enter Password button unless there's
text in both the username and password textboxes.

For instance, you can use each textbox's _Change() event to check the
length of the text in each textbox. If the (trimmed) length is non-zero
in both, enable the button. If not, disable it.
 
K

keithl816

Hi Anne and JE,

Thanks for replying. I have a temporary fix, I entered a fictitious
name in the user name box that when the "enter password button" is
clicked it automatically closes the workbook.

If anybody can see an error in my code please let me know what it might
be?

Again, thank you Anne and JE for your help.
 
J

JE McGimpsey

The only think I can see is that you'll get the same behavior if the
user intentionally or inadvertently deletes your dummy name.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro password-All sheets visible 4
Code to format cells on several worksheets 2
Force Macro 0
Open Specific Form by User Login 1
Making a macro only work on certain files 2
Repeated Requests for GSuite password when first Accessing Outlook 365 after system start 0
VBA for Dependents shortcut menu 0
Drawn Objects Q 2

Top