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
[ ] 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 ListSub auto_open() Worksheets(1).EnableAutoFilter = True Worksheets(1).EnableOutlining = True Worksheets(1).Protect UserInterfaceOnly:=True End SubAnother 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
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 SubColoring protected cells with Styles or Conditional Formatting (doesn't really have anything on C.F. usage in that thread.
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
- Grayed out menus, as a result of protecting a worksheet, many menu items will be grayed out.
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2004, F. David McRitchie, All Rights Reserved