Youtubejust to Hold You Once Again With Exael
Protecting and unprotecting sheets is a common activity for an Excel user. In that location is nothing worse than when somebody, who doesn't know what they're doing, over types essential formulas and cell values. Information technology'south even worse when that person happens to be ourselves; all information technology takes is i accidental keypress, and suddenly the entire worksheet is filled with errors. In this mail service, nosotros explore using VBA to protect and unprotect sheets.
Protection is not foolproof but prevents the accidental amending past an unknowing user.
Sheet protection is particularly frustrating because it has to be practical ane sail at a time. If nosotros simply need to protect a single sheet, that's fine. But if we take more than than 5 sheets, it is going to accept a while. This is why and so many people turn to a VBA solution.
The VBA Lawmaking Snippets below show how to practice well-nigh activities related to protecting and unprotecting sheets.
Download the example file
I recommend you lot download the example file for this mail. Then you lot'll be able to work forth with examples and run into the solution in action, plus the file volition be useful for future reference.
Download the file: 0016 VBA Protect and unprotect sheets.zip
Adapting the code for your purposes
Unless stated otherwise, every instance beneath is based on one specific worksheet. Each code includes Sheets("Sheet1")., this ways the activity will exist applied to that specific sheet. For example, the post-obit protects Sheet1.
Sheets("Sheet1").Protect Only there are lots of means to reference sheets for protecting or unprotecting.
Using the agile sheet
The active sail is whichever sheet is currently existence used within the Excel window.
ActiveSheet.Protect
Applying a canvas to a variable
If we want to apply protection to a sheet stored as a variable, we could employ the following.
Dim ws As Worksheet Set ws = Sheets("Sheet1") ws.Protect
Later in the mail service, we look at code examples to loop through each sail and apply protection quickly.
Protect and unprotect: basic examples
Let'south brainstorm with some simple examples to protect and unprotect sheets.
Protect a sheet without a countersign
Sub ProtectSheet() 'Protect a worksheet Sheets("Sheet1").Protect End Sub
Unprotect a sheet (no countersign)
Sub UnProtectSheet() 'Unprotect a worksheet Sheets("Sheet1").Unprotect End Sub
Protecting and unprotecting with a password
Adding a password to requite an actress 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 exist improve to apply using a cord variable, or capturing user passwords with an InputBox.
Protect canvas with password
Sub ProtectSheetWithPassword() 'Protect worksheet with a countersign Sheets("Sheet1").Protect Password:="myPassword" Cease Sub
Unprotect sail with a password
Sub UnProtectSheetWithPassword() 'Unprotect a worksheet with a password Sheets("Sheet1").Unprotect Password:="myPassword" End Sub
Annotation – It is non necessary to unprotect, then re-protect a sheet to change the settings. Instead, just protect once again with the new settings.
Communicable errors when incorrect countersign entered
If an incorrect password is provided, the following fault message displays.
The code below catches the error and provides a custom bulletin.
Sub CatchErrorForWrongPassword() 'Keep going even if error found On Fault Resume Next 'Apply the wrong password Sheets("Sheet1").Unprotect Password:="incorrectPassword" 'Bank check if an error has occured If Err.Number <> 0 And so MsgBox "The Password Provided is incorrect" Exit Sub End If 'Reset to show normal error messages On Fault GoTo 0 Cease Sub
If you forget a password, don't worry, the protection is easy to remove.
Applying protection to dissimilar parts of the worksheet
VBA provides the power to protect 3 aspects of the worksheet:
- Contents – what you see on the grid
- Objects – the shapes and charts which are on the face of the filigree
- Scenarios – the scenarios contained in the What If Analysis section of the Ribbon
Past default, the standard protect characteristic will utilise all 3 types of protection at the aforementioned time.
Protect contents
Sub ProtectSheetContents() 'Apply worksheet contents protection only Sheets("Sheet1").Protect Countersign:="myPassword", _ DrawingObjects:=False, _ Contents:=True, _ Scenarios:=Simulated Terminate Sub
Protect objects
Sub ProtectSheetObjects() 'Apply worksheet objects protection only Sheets("Sheet1").Protect Countersign:="myPassword", _ DrawingObjects:=Truthful, _ Contents:=Imitation, _ Scenarios:=Faux Finish Sub
Protect scenarios
Sub ProtectSheetScenarios() 'Apply worksheet scenario protection but Sheets("Sheet1").Protect Password:="myPassword", _ DrawingObjects:=Faux, _ Contents:=False, _ Scenarios:=True End Sub
Protect contents, objects and scenarios
Sub ProtectSheetAll() 'Apply worksheet protection to contents, objects and scenarios Sheets("Sheet1").Protect Password:="myPassword", _ DrawingObjects:=True, _ Contents:=True, _ Scenarios:=True Stop Sub
Applying protection to multiple sheets
As nosotros have seen, protection is applied one sheet at a time. Therefore, looping is an excellent way to use settings to a lot of sheets apace. The examples in this section don't merely apply to Sheet1, as the previous examples have, simply include all worksheets or all selected worksheets.
Protect all worksheets in the agile workbook
Sub ProtectAllWorksheets() 'Create a variable to hold worksheets Dim ws As Worksheet 'Loop through each worksheet in the agile workbook For Each ws In ActiveWorkbook.Worksheets 'Protect each worksheet ws.Protect Password:="myPassword" Next ws Cease Sub
Protect the selected sheets in the active workbook
Sub ProtectSelectedWorksheets() Dim ws As Worksheet Dim sheetArray As Variant 'Capture the selected sheets Set sheetArray = ActiveWindow.SelectedSheets 'Loop through each worksheet in the agile workbook For Each ws In sheetArray On Error Resume Next 'Select the worksheet ws.Select 'Protect each worksheet ws.Protect Countersign:="myPassword" On Fault GoTo 0 Next ws sheetArray.Select Terminate Sub
Unprotect all sheets in active workbook
Sub UnprotectAllWorksheets() 'Create a variable to hold worksheets Dim ws As Worksheet 'Loop through each worksheet in the agile workbook For Each ws In ActiveWorkbook.Worksheets 'Unprotect each worksheet ws.Unprotect Countersign:="myPassword" Next ws End Sub
Checking if a worksheet is protected
The codes in this section bank check if each blazon of protection has been applied.
Check if Sail contents is protected
Sub CheckIfSheetContentsProtected() 'Check if worksheets contents is protected If Sheets("Sheet1").ProtectContents Then MsgBox "Protected Contents" Terminate Sub
Check if Sheet objects are protected
Sub CheckIfSheetObjectsProtected() 'Check if worksheet objects are protected If Sheets("Sheet1").ProtectDrawingObjects And then MsgBox "Protected Objects" End Sub
Check if Sheet scenarios are protected
Sub CheckIfSheetScenariosProtected() 'Cheque if worksheet scenarios are protected If Sheets("Sheet1").ProtectScenarios And then MsgBox "Protected Scenarios" End Sub
Changing the locked or unlocked status of cells, objects and scenarios
When a canvass is protected, unlocked items can still be edited. The post-obit codes demonstrate how to lock and unlock ranges, cells, charts, shapes and scenarios.
When the canvas is unprotected, the lock setting has no bear on; each object becomes locked on protection.
All the examples in this section set up each object/item to lock when protected; to unlock, modify the value to False.
Lock a cell
Sub LockACell() 'Changing the options to lock or unlock cells Sheets("Sheet1").Range("A1").Locked = Truthful End Sub
Lock all cells
Sub LockAllCells() 'Changing the options to lock or unlock cells all cells Sheets("Sheet1").Cells.Locked = Truthful End Sub
Lock a chart
Sub LockAChart() 'Changing the options to lock or unlock charts Sheets("Sheet1").ChartObjects("Chart 1").Locked = Truthful End Sub
Lock a shape
Sub LockAShape() 'Changing the pick to lock or unlock shapes Sheets("Sheet1").Shapes("Rectangle 1").Locked = True End Sub
ActiveWorkbook.Worksheets
Sub LockAScenario() 'Changing the option to lock or unlock a scenario Sheets("Sheet1").Scenarios("scenarioName").Locked = True Stop Sub
Allowing deportment to be performed even when protected
Even when protected, nosotros can allow specific operations, such as inserting rows, formatting cells, sorting, etc. These are the aforementioned options equally institute when manually protecting the canvas.
Let sheet deportment when protected
Sub AllowSheetActionsWhenProtected() 'Allowing certain actions even if the worksheet is protected Sheets("Sheet1").Protect Password:="myPassword", _ DrawingObjects:=Faux, _ Contents:=True, _ Scenarios:=False, _ AllowFormattingCells:=True, _ AllowFormattingColumns:=True, _ AllowFormattingRows:=True, _ AllowInsertingColumns:=False, _ AllowInsertingRows:=False, _ AllowInsertingHyperlinks:=Simulated, _ AllowDeletingColumns:=Truthful, _ AllowDeletingRows:=True, _ AllowSorting:=Fake, _ AllowFiltering:=False, _ AllowUsingPivotTables:=False Stop Sub
Allow pick of any cells
Sub AllowSelectionAnyCells() 'Allowing selection of locked or unlocked cells Sheets("Sheet1").EnableSelection = xlNoRestrictions Terminate Sub
Allow selection of unlocked cells
Sub AllowSelectionUnlockedCells() 'Assuasive selection of unlocked cells only Sheets("Sheet1").EnableSelection = xlUnlockedCells End Sub
Don't permit selection of whatsoever cells
Sub NoSelectionAllowed() 'Practise not allow choice of any cells Sheets("Sheet1").EnableSelection = xlNoSelection End Sub
Assuasive VBA code to brand changes, even when protected
Even when protected, we still want our macros to make changes to the sheet. The following VBA code changes the setting to allow macros to make changes to a protected sheet.
Sub AllowVBAChangesOnProtectedSheet() 'Enable changes to worksheet by VBA lawmaking, even if protected Sheets("Sheet1").Protect Password:="myPassword", _ UserInterfaceOnly:=Truthful Terminate Sub
Allowing the use of the Group and Ungroup feature
To enable users to brand use of the Group and Ungroup feature of protected sheets, we need to allow changes to the user interface and enable outlining.
Sub AllowGroupingAndUngroupOnProtectedSheet() 'Let user to group and ungroup whilst protected Sheets("Sheet1").Protect Password:="myPassword", _ UserInterfaceOnly:=Truthful Sheets("Sheets1").EnableOutlining = True End Sub
Conclusion
Wow!That was a lot of lawmaking examples; hopefully, this covers everything you would ever need for using VBA to protect and unprotect sheets.
Get our Gratis VBA eBook of the 30 nearly useful Excel VBA macros.
Automate Excel and so that you tin can salve time and cease doing the jobs a trained monkey could do.
Claim your costless eBook
Don't forget:
If you've found this post useful, or if you take a ameliorate approach, so please leave a comment below.
Exercise y'all need help adapting this to your needs?
I'one thousand guessing the examples in this mail didn't exactly meet your situation. We all use Excel differently, then information technology'south incommunicable to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should exist able to adapt it to your needs.
Merely, if you're still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more than by discovering your ain solutions.
- Ask the 'Excel Ninja' in your role. Information technology's amazing what things other people know.
- Inquire a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So accept care to craft your question, brand sure it's clear and concise. List all the things you've tried, and provide screenshots, lawmaking segments and case workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:
Source: https://exceloffthegrid.com/vba-code-worksheet-protection/
0 Response to "Youtubejust to Hold You Once Again With Exael"
Postar um comentário