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