Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles vba Protect workbook How to Protect Workbook using VBA – Excelchat

How to Protect Workbook using VBA – Excelchat

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

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc