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

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" wrote
in message ...

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

Hi Anne,

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

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

I'm the main user, the only time anyone else uses it, is if I am on
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 a
select few.

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 an error pops up. If you choose to click
the end button in the open dialogue box instead of debug it continues
without closing, exposing the password and userlog.

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

Again, thanks for replying, any help is appreciated.


--
keithl816
------------------------------------------------------------------------
keithl816's Profile: http://www.excelforum.com/member.php...o&userid=21287
View this thread: http://www.excelforum.com/showthread...hreadid=385903

Doesn't seem like the code is right, of course. Maybe you can try something
else over at VBAX. They have a couple KB entries with user
logging/protection/management.
Here's the full list: http://www.vbaexpress.com/kb/kblist.php
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com




"keithl816" wrote
in message ...

Hi Anne,

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

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

I'm the main user, the only time anyone else uses it, is if I am on
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 a
select few.

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 an error pops up. If you choose to click
the end button in the open dialogue box instead of debug it continues
without closing, exposing the password and userlog.

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

Again, thanks for replying, any help is appreciated.


--
keithl816
------------------------------------------------------------------------
keithl816's Profile:
http://www.excelforum.com/member.php...o&userid=21287
View this thread: http://www.excelforum.com/showthread...hreadid=385903

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.


In article ,
keithl816
wrote:

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 an error pops up. If you choose to click
the end button in the open dialogue box instead of debug it continues
without closing, exposing the password and userlog.

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.


--
keithl816
------------------------------------------------------------------------
keithl816's Profile: http://www.excelforum.com/member.php...o&userid=21287
View this thread: http://www.excelforum.com/showthread...hreadid=385903

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


In article ,
keithl816
wrote:

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?