Thanks in advance for your help. Users can change filter criteria but can not enable or disable an auto filter. Min 255, B With Worksheets 1. There are three levels of Sheet protection in the user interface. As soon as you close it and re-open it you'll be able to select any cells again. But the function works fine for me if the worksheet is unprotected. Scenarios Optional Variant True to protect scenarios.
AllowFormattingRows: Its Optional parameter and Boolean type Input. I've added the code from Smitty, which works fine for unprotecting the template so I can paste, and then protects it. The default value is True. Tom Thanks for your help, Dave, but it didn't work. It should actually be ActiveSheet.
I suspect chart-specific protection functions aren't supported in 2011, although I can't locate any 2011 documentation or online discussions that confirm that. Maybe it can't unprotect anything -- I'm not sure yet. For a worksheet, this protects the individual cells. Contents Optional Variant True to protect contents. Lock and protect selected cells from viewing by encrypting 1.
UserInterfaceOnly : Its Optional parameter and Boolean type Input. All cells that are Locked are fully protected when Sheet protection is applied and includes Contents. AllowInsertingRows: Its Optional parameter and Boolean type Input. DrawingObjects Optional Variant True to protect shapes. AllowInsertingColumns: Its Optional parameter and Boolean type Input. File-level protection is not the same as or. AllowFiltering Optional Variant True allows the user to set filters on the protected worksheet.
AllowFormattingCells: Its Optional parameter and Boolean type Input. I've made progress, but have one 'minor' problem. See screenshot: Then a dialog box comes out to tell you selected cells have been unlocked. But when I go into the resulting worksheet, it is 'protected', but the user can select locked cells. The unprotect command also works. End Sub If the Worksheet was Unprotected when the file was last saved, it becomes Protected when the file is opened and macros are enabled. This site uses Akismet to reduce spam.
I'm not protecting it for security, just to restrict where people key in information. The default value is False. The spreadsheet is then to be distributed to other offices where some designated cells are to be amended. Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel 1. Next time, when you open this workbook and try to view the code, you will be asked for the password to enter the code. Select ' unlock all the cells Selection.
And now it locks and protects only cells and ranges you selected in the Step 4, while unselect ranges are editable. End Sub Did you confirm that Protect statement works for you? This site contains affiliate links. Then, hide the column or row that contains the linked cell, or move the contents of the linked cell to a separate worksheet, and then hide that worksheet. Any affiliate commissions that we earn when you click a link to Amazon or other sites is reinvested in keeping MrExcel. I am creating workbookA by taking a templateA and pasting in information from workbookB. When working Worksheet code has password protected, if you want to do any changes in the code first you have to unprotect the password at the beginning of the procedure and at the end of the procedure you have to protect it again.
AllowFormattingColumns: Its Optional parameter and Boolean type Input. The version I downloaded from you produces the same error for me, even after rebooting. Store the passwords that you write down in a secure place away from the information that they help protect. Save the workbook as Excel Macro-Enabled Workbook format and close it to make the password protection take effect. How can I create the worksheet to allow these cells to be amended when the whole sheet is protected? Protect Exit Sub ErrHandler: MsgBox Err.
Does it error for you in that case? However, sometimes you may need to lock and protect only specific cells or selections in a sheet. When I ran the Protect routine, the entire worksheet got protected, not just the cells in the Range command. Full feature free trial 60-day, no credit card required! It's easy to lock and protect the whole worksheet or workbook with clicking the Protect Sheet button or Protect Workbook button under Review tab. Further, the cells in the newly added worksheet weren't protected and the cells in the original worksheet did not get re-protected. Note:To apply sort every cell in the range must be unlocked. So at the end of all that you may like to try: Sub ProtectTheSheet Cells.