Need advanced Excel, VBA or SQL support? Outsource your project now!
Connect to our Experts within seconds for a free diagnosis
Our professional experts are available now. Your privacy is guaranteed.

How to Protect Workbook using VBA

We can protect our workbook using a created VBA macro rather than protect only a single worksheet. This prevents unauthorised access to the content of the workbook. This tutorial will teach us how to protect our workbook using excel VBA.

Figure 1: Password protect macro

How to Protect Workbook with Password 

To use VBA to protect our workbook, we need to enable the Developer tab


  • We will click on any of the Menu tab options like Review


Figure 2: Excel VBA protect sheet


  • We will right-click on Review and click Customise the Ribbon


  • In the Excel options dialog box, we will check the Developer box in the Main tabs section at the right.
  • We will click OK



Figure 3: Enabled Developer tab to protect with vba code

Record Macros

Before protecting the workbook, we have to record the macro. To do this, we will do the following:

  • We will click the Developer tab and select Record Macro

Figure 4: Record Macro

  • In the Record Macro dialog box, we will choose a macro name and select where we will store our macro and click OK

Figure 5: Record Macro dialog box

Protect Workbook

  • We will click on Visual Basic

Figure 6: Click on Visual Basic

  • If we do not have a created module, we will right-click on VBAProject (Personal.XLSB), click on Insert and select Module

Figure 7: Creating a Module

  • We will click on Module 1 and insert the code below

<strong>Sub ProtectSheets()

Dim wsheet As Worksheet

For Each wsheet In ActiveWorkbook.Worksheets

wsheet.Protect Password:="password"

Next wsheet

End Sub</strong>

  • We will close the VBA window
  • We will assign the macro to the quick access toolbar
  • We will click on the drop-down arrow beside the redo icon

Figure 8: Adding macro to quick access toolbar


  • We will click on More commands


  • In the Choose command from section, we will select macros, click the Personal.XLSB!PersonalMacroWorkbook and select Add



Figure 9: Excel options dialog box

  • We will click the Personal.XLSB!PersonalMacroWorkbook on the right and click Modify below

Figure 10: Modify Button

  • We will select a button and click OK to view the button in the quick access toolbar

Figure 11: Selected button

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Comment


Subscribe to

Get updates on helpful Excel topics

Subscribe to
Trusted by people who work at, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc