Protection, Worksheet

Worksheet Protection

Worksheet Protection is mainly to prevent serious keying in errors that could damage the worksheet rather than security against reading content.

By default all cells are protected but protection does not take effect until the sheet itself is protected.  Formulas can be protected from viewing by additionally checking

Format --> Cells --> Protection
    [x] locked
    [  ] hidden

For the above if you want to be able to make changes to a cell select the group and uncheck locked.

If you want to hide formula from view, choose locked & hidden

Nothing takes effect until you protect the sheet.

Tools --> Protection --> Protect sheet
you do not need to use a password, if you do use a password make sure that you and whoever the workbook is turned over will remember the password  If you do use a password you can turn it off/on in VBA to make changes as needed.

Check out protection in HELP   (#help)

You can select ALL cells (ctrl+a) then use Edit, GoTo, Special to select only formulas or only constants with refinements as to which onees to help you change protection under format for selected cells.

Turn off/on password protection within a Macro (#password)

Worksheets("Mysheet").Unprotect Password:= "mypassword"
'ooo your other code here ooo
Worksheets("Mysheet").Protect Password:= "mypassword"

To unlock a project, Tim Zych

User Interface     (#userinterface)

Limiting protection to User Interface, allows one to Filter a List

Sub auto_open()
  Worksheets(1).EnableAutoFilter = True
  Worksheets(1).EnableOutlining = True
  Worksheets(1).Protect UserInterfaceOnly:=True
End Sub

Another example of protection via the UserInterface:

ActiveSheet.Protect password:="secret", DrawingObjects:=True, _
   Contents:=True, Scenarios:=True, UserInterfaceOnly:=True

Code protection   (#code)

If only those who know the password should be able to run a particular macro:

  Dim strResponse As String
  strResponse = Application.Dialogs(xlDialogProtectDocument).Show
  If strResponse = "False" Then Exit Sub

• Granting permission to only view certain portions of a worksheet, Dave Peterson, misc, 2002-05-30

Workbook Protection     (#workbook)

An interesting (scary) thing is that Tools, Protection, Workbook, Windows will remove the minimize/maximize/restore/close buttons in upper right corner [x].

Hiding Sheets (#hidesheets)

    Worksheets(Array("Sheet1","Sheet2")).Visible = xlHidden
    Worksheets(Array("Sheet1","Sheet2")).Visible = xlsheetvisible

Color Protected Cells (#color)

Protection must be off when running this macro.

  Sub ShadeProtected()  'Set cell shading for selection
  Dim cell As Range
  Application.ScreenUpdating = False
  For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
     If cell.Locked Then
         cell.Interior.ColorIndex = 36  'lt.yellow
     Else    '(not locked)          
     End If
  Next cell
  Application.ScreenUpdating = True
  End Sub

Coloring protected cells with Styles or Conditional Formatting (doesn't really have anything on C.F. usage in that thread.

Navigation, Using Cell Protection to Restrict TAB key selection (#navigation)

You can use cell protection to prevent a cell from being selected. Cell protection is on by default, but not active unless you protect the sheet.
  Select columns A, C, E, G, I Format, Cells, Protection, uncheck unlock
 
Select columns B, D, F, H, J:IV
Use fill-bucket to color interior of cells yellow
Select all cells (Ctrl+A)
Format, Cells, borders, choose inside and outside borders
    (also available on a tool bar button)
 
Tools, protection, protect sheet (password not required)
    (to undo use Tools, protection, unprotect)
 
To test drive: Use the TAB key you will go across to next
unprotected cell on same row. Use the Enter key and
you will move to next row. You can change the direction
of the Enter key, but the default is Down.
 
You can refine the above with the use of an Event Macro
see http://www.mvps.org/dmcritchie/excel/event.htm

Problems     (#problems)

• Grayed out menus, as a result of protecting a worksheet, many menu items will be grayed out.

Related     (#related)