Protect Method of Worksheet Object VBA

When we are dealing with confidential information or data we need to Protect Worksheet from the user to edit data, delete data, and move data in Excel VBA. If user wants to see data in Worksheet user should have password to view data in the Worksheet. We need to protect Worksheet at the end of the procedure once changes has done to the Worksheet using VBA. If you forgot the password, you cannot UnProtect the Worksheet.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


Why we use Protect Worksheet Method using VBA?

When Worksheet has sensitive data to deal with confidential information or data we need to Protect Worksheet in Excel VBA using protect method of worksheet object. You should protect your complete Worksheet then only authorized users can view or modify the data.

VBA Protect Worksheet Method – Syntax

Here is the syntax to Protect Worksheet using VBA.

Worksheets(“Your Worksheet Name”). Protect(

[Password], [DrawingObjects], [Contents], [Scenarios], [UserInterfaceOnly], [AllowFormattingCells], [AllowFormattingColumns], [AllowFormattingRows], [AllowInsertingColumns], [AllowInsertingRows], [AllowInsertingHyperlinks], [AllowDeletingColumns], [AllowDeletingRows], [AllowSorting], [AllowFiltering], [AllowUsingPivotTables])

Where
Password: Its Optional parameter. It specifies a case sensitive password for the Worksheet. If omitted then Worksheet will be locked without a password and while unprotecting it, user will not be asked for any password. When you want to unprotect your protected Worksheet you must specify your password.
DrawingObjects: Its Optional parameter and Boolean type Input. Default value is TRUE. If the value is True it allows to protect all shapes in the Sheet.
Contents: Its Optional parameter and Boolean type Input. Default Value is TRUE. If the value is TRUE to protect contents of the Worksheet except those cells or range which are set as “Un-Locked”.
Scenarios: Its Optional parameter and Boolean type Input. The default value is True. If the value is True then it protect scenarios.
UserInterfaceOnly : Its Optional parameter and Boolean type Input. If the value is True to protect the user interface, but not macros. If this parameter is omitted, protection applies both to macros and to the user interface.
AllowFormattingCells: Its Optional parameter and Boolean type Input. Default Value is FALSE. If the value is TRUE then it allows user to format any cell on a protected worksheet.
AllowFormattingColumns: Its Optional parameter and Boolean type Input. Default Value is FALSE. If the value is TRUE then it allows user to format any Column on a protected worksheet.
AllowFormattingRows: Its Optional parameter and Boolean type Input. Default Value is FALSE. If the value is TRUE then it allows user to format any Row of the protected worksheet.
AllowInsertingColumns: Its Optional parameter and Boolean type Input. Default Value is FALSE. If the value is TRUE then it allows user to insert column in the protected worksheet.
AllowInsertingRows: Its Optional parameter and Boolean type Input. Default Value is FALSE. If the value is TRUE then it allows user to insert Rows in the protected worksheet.
AllowInsertingHyperlinks: Its Optional parameter and Boolean type Input. Default Value is FALSE. If the value is TRUE then it allows user to insert Hyperlinks on the protected worksheet.
AllowDeletingColumns: Its Optional parameter and Boolean type Input. Default Value is FALSE. If the value is TRUE then it allows user to delete columns from the protected worksheet.
AllowDeletingRows: Its Optional parameter and Boolean type Input. Default Value is FALSE. If the value is TRUE then it allows user to delete rows from the protected worksheet.
AllowSorting: Its Optional parameter and Boolean type Input. Default Value is FALSE. If the value is TRUE then it allows user to sort data in the protected worksheet. Note:To apply sort every cell in the range must be unlocked.
AllowFiltering: Its Optional parameter and Boolean type Input. Default Value is FALSE. This will allow user to change the filter criteria but cannot enable or disable the AUTO-FILTER option in worksheet. Users are allowed just to change the different filters on an already existing auto-filter.
AllowUsingPivotTables: Its Optional parameter and Boolean type Input. Default Value is FALSE. If the value is TRUE then it allows the user to use pivot table reports on the protected worksheet.
In the above syntax we are using ‘Protect’ method of Worksheet object to protect the Worksheet.

VBA Protect Worksheet Method – Without Password

Please find the below example, It will show you how to protect the Worksheet without password.

Sub Protect_WorkSheet_Without_Password()
Sheets("Sheet1").Protect
End Sub

Explanation: In the above example we are protecting the Worksheet without password by using ‘Protect’ method of Worksheet object in the worksheet named ‘Sheet1’. In the above procedure we are not providing any password, so that the worksheet will be locked for editing. While unprotecting same worksheet it won’t ask any password to unprotect the worksheet.

VBA Protect Worksheet Method – With Password

Here is the example to protect Worksheet with password.

Sub Protect_WorkSheet_With_Password()
Sheets("Sheet1").Protect "YourPassword", True, True
End Sub

Explanation: In the above example we are protecting the Worksheet with password by using ‘Protect’ method of Worksheet object in the worksheet named ‘Sheet1’. 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. While unprotecting same worksheet it will ask for the password to unprotect the worksheet.

VBA Protect Worksheet Method: Instructions

Please follow the below step by step instructions to execute the above mentioned VBA macros or codes:

  1. Open an Excel Worksheet
  2. Press Alt+F11 :This will Open the VBA Editor. Otherwise, you can open it from the Developer Tab
  3. Insert a Module from Insert Menu
  4. Copy the above code for activating worksheet and Paste in the code window(VBA Editor)
  5. Save the file as macro enabled Worksheet
  6. Press ‘F5’ to run it or Keep Pressing ‘F8’ to debug the code line by line and check how the Worksheet has Protected.
LIMITED TIME OFFER

Leave A Comment


Related pages


excel macro formatvb filecopymacro for vlookupvba file objectunlock excel workbook forgot passwordlocking formulas in excel 2010tcl in sqlexcel unlock password protected fileexcel vba project passwordexcel userforms for data entryduplicate remover for excelexcel unsharemsgbox function in vbvba array uboundhow to build a dashboard in excelvba print sheetexcel formula sort alphabeticallycharttitleworkbooks.close vbaexcel vba vlookupvba connectionsas code for beginnersvba sheetmacro excel programminglistbox stylessql questions and answers pdfhow to shade cells in excelhow do i create a dashboard in excelexcel macro rename filevba gotousing hlookup in excelexcel 2010 find duplicatesexcel vba code examples pdfexcel sql tablerange in excel macromerge tabs in excelprogramming excel macrosindirect function in vbaunprotect an excel sheet without passwordbasic excel macro exampleshow to remove columns in excelsas programming for dummiesworkbook.worksheetsvb script interview questions and answers for freshersspeed up vbamicrosoft office project plan templatevlookup duplicateshard return excelexcel shortcut for insert rowwhat is a stock chart in excelexcel to kml macroconvert excel column width to inchesqc check listvba private functionexcel macro import text fileeasy vlookup tutorialvlookup function exceluses of macros in excelvba integer to stringdim conn as new adodb connectioninsert checkbox in powerpoint 2010project dashboard excel template freemicrosoft excel project timeline templatemacro to combine worksheetstrim in vbauses of macros in excelproject management costing templatewhat is vlookup function in excelunhide rowsvba excel trimhow to use vlookup 2010select case statement vbaexcel 2010 autofit row heightms excel formulas list with examplesuseful excel macropublic variable vbaexcel quick tutorialfilter macro excel