![]() Therefore, the codes in this section provide examples of using VBA to protect and unprotect based on user input. Using a password that is included in the code may partly defeat the benefit of having a password. NOTE – It is not necessary to unprotect, then re-protect a sheet to change the settings. Instead, just protect again with the new settings. Sheets("Sheet1").Unprotect Password:="myPassword" Sheets("Sheet1").Protect Password:="myPassword"Įnd Sub VBA Unprotect sheet with a password Sub UnProtectSheetWithPassword() VBA Protect sheet with password Sub ProtectSheetWithPassword() Protect a sheet without a password Sub ProtectSheet()Įnd Sub Unprotect a sheet (no password) Sub UnProtectSheet()Įnd Sub Protecting and unprotecting with a passwordĪdding a password to give an extra layer of protection is easy enough with VBA. The password in these examples is hardcoded into the macro this may not be the best for your scenario. It may be better to apply using a string variable, or capturing user passwords with an InputBox. Let’s begin with some simple examples to protect and unprotect sheets in Excel. Later in the post, we look at code examples to loop through each sheet and apply protection quickly. If we want to apply protection to a sheet stored as a variable, we could use the following. The active sheet is whichever sheet is currently being used within the Excel window. Therefore we can change the syntax to use one of the methods shown below. Sheets("Sheet1").Protectīut there are lots of ways to reference sheets for protecting or unprotecting. Unless stated otherwise, every example below is based on one specific worksheet. Each code includes Sheets(“Sheet1”)., this means the action will be applied to that specific sheet. For example, the following protects Sheet1. Get access Adapting the code for your purposes Allowing the use of the Group and Ungroup feature. ![]() Allowing VBA code to make changes, even when protected.Allowing actions to be performed even when protected.Changing the locked or unlocked status of cells, objects and scenarios.Unprotect all sheets in active workbook.Protect the selected sheets in the active workbook.Protect all worksheets in the active workbook.Protect contents, objects and scenarios. ![]() Applying protection to different parts of the worksheet.Catching errors when incorrect password entered.Protecting and unprotecting with a password.The VBA Code Snippets below show how to do most activities related to protecting and unprotecting sheets. ![]() ![]() Sheet protection is particularly frustrating as it has to be applied one sheet at a time. If we only need to protect a single sheet, that’s fine. But if we have more than 5 sheets, it is going to take a while. This is why so many people turn to a VBA solution. Protection is not foolproof but prevents accidental alteration by an unknowing user. It’s even worse when that person happens to be us all it takes is one accidental keypress, and suddenly the entire worksheet is filled with errors. In this post, we explore using VBA to protect and unprotect sheets. Protecting and unprotecting sheets is a common action for an Excel user. There is nothing worse than when somebody, who doesn’t know what they’re doing, overtypes essential formulas and cell values. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |