Here is how I have my Excel file setup:
Here is my problem:
All the users with a username and password could easily change the protection once the file is unprotected. So, I would like everyone to be able to open the workbook in protected mode, but when the username and password is entered, I would want the workbook to be still in protected mode, but to be able to edit a particular range of cells, e.g., A10:D10, and not be able to edit objects. This way, since the workbook is still in protected mode, they will not be able to make any changes to the protection.
This is what I have under ThisWorkbook:
This is what I have in the userform that prompts for access:
I just wanted some more input, so I've asked the same questions in this forum:
http://www.ozgrid.com/forum/showthread.php?t=176709
- It is opened in protected mode, except one worksheet called "User Names", which is set to xlsheetveryhidden.
- A set of usernames and passwords are stored the worksheet called "User Names". When a user enters his for her password, it loops through "User Names" worksheet and if a match is found, the workbook is unprotected.
Here is my problem:
All the users with a username and password could easily change the protection once the file is unprotected. So, I would like everyone to be able to open the workbook in protected mode, but when the username and password is entered, I would want the workbook to be still in protected mode, but to be able to edit a particular range of cells, e.g., A10:D10, and not be able to edit objects. This way, since the workbook is still in protected mode, they will not be able to make any changes to the protection.
This is what I have under ThisWorkbook:
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
For Each ws In Sheets
If ws.Name <> "User Names" Then
ws.Protect Password:="pass1"
End If
Next
If Not ActiveWorkbook.Saved Then
ActiveWorkbook.Save
End If
End Sub
This is what I have in the userform that prompts for access:
Code:
Private Sub btnAccess_Click()
' Other code here
' Once a username and password is matched, the workbook is inprotected.
For Each ws In Sheets
ws.Unprotect Password:="pass1"
Next
End Sub
I just wanted some more input, so I've asked the same questions in this forum:
http://www.ozgrid.com/forum/showthread.php?t=176709