Protect Method of Workbook Object VBA

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

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 need to Protect a Workbook using VBA?

When workbook has sensitive data to deal with confidential information or data we need to Protect Workbook in Excel VBA. You should protect your complete workbook then only authorized users can view or modify the data.

VBA Protect Workbook – Syntax

Here is the syntax to Protect workbook using VBA.

Workbooks(“Your Workbook Name”).Protect(

[Password], [Structure], [Windows])

Where
Password: Its Optional argument. It specifies a case sensitive password for the workbook. If we want to protect workbook then you have to mention password argument, otherwise you can omit it. When you want to unprotect your protected workbook you must specify your password.
Structure: Its Optional parameter. The default value is False. If we mention it as True that means protect the workbook structure.
Windows: Its Optional parameter. The default value is False. If we mention it as True that means protect the workbook windows.
In the above syntax we are using ‘Protect’ method of workbook object to protect the workbook.

VBA Protect Workbook: Example 1

Please find the below example, It will show you how to protect the workbook.

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

In the above example we are protecting the workbook by using ‘Protect’ method of Worksheet object in the worksheet named ‘Sheet1’. When working workbook 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.

VBA Protect Workbook: Example 2

Here is the example to protect ActiveWorkbook with password.

Sub Protect_Sheet()
ActiveWorkbook.Protect Password:="YourPassword", Structure:=True, Windows:=True
End Sub

VBA Protect Workbook – Instructions

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

  1. Open an Excel Workbook
  2. Press Alt+F11 to Open VBA Editor
  3. Insert a Module from Insert Menu
  4. Copy the above code for activating a range and Paste in the code window(VBA Editor)
  5. Save the file as macro enabled workbook
  6. Press ‘F5’ to run it or Keep Pressing ‘F8’ to debug the code line by line.
LIMITED TIME OFFER

Leave A Comment


Related pages


vlookup macro excelsql query excel spreadsheetvba filter criteriaobject oriented vbaexcel checkbookloops excelexcel newline in formulaexcel vba made easysql ddl and dml commandsfrequently asked interview questions in sqloffset function in vbaexcel vba merge workbooksmacro dialog boxlearning sas by example data setsmacro writing excelexcel vba hide worksheetmsdasql providernull vbamatch formula in excel 2007set row height in excel 2010unlock protected excel workbookthe tutorial on excel macros vbaconstruction schedule excel templatecopy and paste in vbaproper formula in excelmatch vba excelconcatenate in vbaexcel vba sortmarketing gantt chart templatevb syntaxexcel.range vb.netdynamic pivot tablesexcel macro duplicate sheethow to change excel columns to rowsrecordset getrows vbaremoving duplicates in excel 2003vba excel programming pdfunhiding cells in excelexcel rept functiondelete rows in excelpivot tables excel tutorialexcel formula cell colorexcel repeat last actionif elseif vbafor each loop vbavba meaningvba open file for readingexamples of formulas in excelhow do you create a macro in excelexcel syntaxexcel shortcuts formulasexcel vba sample projectsvbyesnocancelremove duplicate excel 2010excel table pivotwhere is autofit in excel 2013excel vba returnlen function in excelpassword protection excel 2010vba check if file existsexcel unhide sheetexcel vlookup examplesprogramming logic questions and answersexcel vba open files in foldermacros and vbafind duplicate entries in excelvba combobox rowsourcevba dsn connection stringexample of worksheet in excelexcel export to txtinsert a macro in excelmacros in vbaproject timeline excel templatehow to merge cell data in excelvbscript copy folderhow to unhide multiple rows in excelexcel developer tab missingspider web graph excelhow to add entire column in excelexcel vba loop