Protecting and unprotecting worksheets

Shanthan

New Member
Joined
Feb 24, 2013
Messages
47
Here is how I have my Excel file setup:

  • 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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Why can't you just unlock the cells you want to allow the user to edit?
 
Upvote 0
I am trying to do this for my workplace schedule. I would like the schedule to be used for viewing purposes only, and have only the managers be able to edit it. When the managers make changes due to trades, sick calls, etc., I have put in a standardized comments using user forms to automatically generate cell comments. To keep track of which manager is making the changes, I have used the username and password solution. In some cases, employees might have to edit it, which will be in rare circumstances. Same rules apply to them, but I would like the employees to only be able to edit certain ranges of cells, and they should not be able to make chances to the workbook protection.
 
Upvote 0
So, why not unlock the cells that others can safely edit and keep the worksheet protected at all times?
 
Upvote 0
It's because when people scroll around and look at the schedule, I don't want them to accidently change a cell value, and get the user form that pops up when a cell value is changed.

Is there another way besides making use of worksheet protection to prevent changes to cell value unless the user is logged in?
 
Last edited:
Upvote 0
It's because when people scroll around and look at the schedule, I don't want them to accidently change a cell value, and get the user form that pops up when a cell value is changed.
If the sheet is protected they can't change any cell that is locked, only cells that are unlocked. Is that not what you want?
 
Upvote 0
Yes, I would like users to only be able to edit cells that are unlocked. I would also like object editing to be disabled.
 
Upvote 0
Well that would be the case if you unlock the cells for which editing is allowed and do not remove worksheet protection.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top