ProtectSharing Method of Workbook Object VBA

ProtectSharing Workbook Method in VBA is used to Saves the workbook and protects it for sharing. When shared workbook has protected, if you want to do any modifications we need to unshared and unprotect workbook at the beginning of the procedure to do any changes to the workbook. If you forgot the password, you cannot unshared or unprotect the workbook.



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 ProtectSharing a Workbook using VBA?

We need to protect shared workbook using VBA to saves the workbook and protect the workbook for sharing to confidential information securely.

VBA ProtectSharing Workbook – Syntax

Here is the syntax to protectSharing workbook using VBA.

Workbooks(“Your Workbook Name”).ProtectSharing(

[Filename], [Password], [WriteResPassword], [ReadOnlyRecommended], [CreateBackup], [SharingPassword], [FileFormat])

Filename: Its Optional parameter. It indicates the name of the file path.
Password: Its Optional parameter. Password is Case sensitive and no longer than 15 characters. It indicates the password for the file to protect shared workbook. Use Strong password.
WriteResPassword: Its Optional parameter. It indicates if a file is saved with the password and the password is not provided when the file is opened, then the file is opened read only file.
ReadOnlyRecommended: Its Optional parameter. When this value is ‘True’, it will display a message when the file is opened and it will recommended that the file be opened read only.
CreateBackup: Its Optional parameter. If the value is ‘True’, then it creates backup file.
SharingPassword: Its Optional parameter. It is used to protect the file for sharing.
FileFormat: Its Optional parameter. It indicates the file format.

VBA ProtectSharing Workbook:Example 1

Please find the below example, It will show you how to protect workbook when the file has shared.

Sub protect_Sharing()
ActiveWorkbook.protectSharing Password:=”YourPassword”, SharingPassword:=”SharedPassword”
End Sub

In the above example we are protecting shared workbook by using ‘protectSharing’ method of Workbook object in the active workbook. When working with workbook if it has password protected and shared, if you want to do any changes in the code first you have to unshare and unprotect the password at the beginning of the procedure.

VBA ProtectSharing Workbook:Example 2

Here is the one more example to protect shared workbook.

Sub Protect_Share()
Dim Wkb As Workbook
Set Wkb = Workbooks.Open(“C:\Sample.xlsm”)
Wkb.ProtectSharing “C:\Sample.xlsm”, “Pwd1”, “Pwd2”,, True, “Pwd3”
End Sub

VBA ProtectSharing Workbook: Remove Sharing

Here is the one more example to remove sharing to the workbook.

Sub Workbook_Remove_Share()
Dim Wkb As Workbook
Set Wkb = Workbooks.Open(“C:\Sample.xlsm”,,,, “Pwd1”, “Pwd2”)
Wkb.UnprotectSharing “Pwd3”
Wkb.WritePassword = “”
Wkb.Password = “”
End Sub

In this example we are removing sharing password of workbook for the above macro (Example 2).

VBA ProtectSharing 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.

Leave A Comment

Related pages

how to concatenate in vbavba color codeexcel lookup arrayit project budget template excelvba code to clear cellsvba excel columnsvlookup function in excel with examplevba application screenupdating falseapplication displayalertscombo box visual basicexcel function sumifconcatenate excel worksheetsrept excelexcel vba switchmacro vba excelsample excel spreadsheet templatesvba excel button propertiespassword worksheetexcel tutorial examplesexcel vba unprotect sheet without passwordvba delete worksheetexcel vba delete columnsddl queriesmacros in excel sheetworkbook close vbamacro to unprotect workbookexcel task tracker templateexcel vba tutorialshow to de dupe in excelvba chart titlewhat is a vlookup function in excelmeaning of vlookup and hlookupexcel formula sort alphabeticallyadodb connection string sql serverunlock password protected excel spreadsheetexcel data from another workbookmultiple if statement exceldynamic named range excel 2010vba cell referenceexcel vba refresh pivot tableinsertrangebest excel formatvarious sql commandsnutshell tutorialaddition in columns worksheetsexcel unlock workbookinsert a check box in excelvlookup macro excelhow to pivot table excel 2013using vlookup in vbainsurance dashboard examplefso vbaexcel vba sampleexcel vba messageexcel 2007 userformlearn excel macros step by stepvba excel direxcel carriage returncopy excel workbookvba subroutinesvba update sqlinsert tick box in excel 2007how to insert hyperlink in excelhow to put alphabetical order in excelhow to calculate the difference between two dates in excelhow to insert multiple columns in excelworkbook.openvba excel sheetshow to create combobox in excel 2010types of excel graphssheets in vbahlookup example excel 2010how do i hide a column in excelexcel vba open excel filemultiple if statements in excel 2010how to disable a macro in excelactivate workbook