In this Article Show
This tutorial will teach you everything about Excel Worksheet protection in VBA – How to Protect or Unprotect Worksheets in Excel. Unprotect Excel Worksheet Without PasswordTo unprotect a Worksheet that isn’t password-protected use this simple line of code:
Unprotect Excel Worksheet With PasswordTo unprotect a Worksheet that’s password-protected, you must also enter the password:
Unprotect Sheet – Lost PasswordTo unprotect a Worksheet without knowing the password, you must use a password recovery add-in. Protect WorksheetsWorksheet Protection allows you to lock certain aspects of the sheet from editing. This menu is found in Home > Format > Protect sheet or by right-clicking on the Sheet tab name: Most commonly this is used to protect “Locked” cells from editing, only allowing the end-user to edit certain cells. You can lock cells by selecting them, and opening the Protection tab of the Cell Formatting Menu (CTRL + 1). You can also prevent the user from changing the worksheet structure (inserting, deleting, or resizing Rows & Columns), or from interacting with AutoFilters, and much more. Protect Worksheet – Without PasswordYou might want to protect a Worksheet without entering a password. This will prevent accidental changes to the worksheet, while giving the user access to make changes if desired.
Protect Worksheet – Password Protect
VBA Coding Made EasyStop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! Learn More!! Protect Worksheet SettingsThe above examples will protect Worksheets with the standard protection settings. Instead you might want to customize what is protected:
Instead of using the syntax above, I recommend recording a Macro with your desired settings (chosen with the Worksheet Protection menu above) and copying + pasting the recorded code into your procedure. Protect Sheet – Allow VBA to Make ChangesBy default, when you protect a sheet, the protection applies to VBA operations in addition to user actions. If VBA attempts to modify a locked cell, you will see a runtime error 1004. To avoid this, you could unprotect and re-protect your worksheets whenever VBA needs to interact with them:
However, it’s easy to forget to unprotect and/or re-protect your worksheets. This can can increase the probability of a coding error. Instead, you can use the UserInterFaceOnly setting. When TRUE, worksheets will ONLY be protected from users, NOT from VBA. Your VBA code will be free to edit the worksheet just like if it was unlocked. Two important points about UserInterFaceOnly:
So in order to set the UserInterFaceOnly property, you should place the following Workbook_Open event procedure in ThisWorkbook module:
Workbook_Open is a special event procedure that will run each time the workbook is open. It must be placed in the ThisWorkbook module. Alternatively, you could use the Auto_Open event procedure (not covered here). Unprotect All Sheets MacroThis Macro will unprotect all Sheets in a workbook:
VBA Programming | Code Generator does work for you! Protect All Sheets MacroThis Macro will protect all Sheets in a Workbook: |