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.