How To Protect And Unprotect Sheets Using An Excel Macro

 green Excel logo

A spreadsheet can be protected so that editing using the same password is inhibited. In today’s tutorial we are going to count sheets protect and unprotect an Excel workbook using Macro.

How to Protect and Unprotect sheets Using an Excel Macro

Creating the Macro in Excel in few steps

You may also be interested in:

First to explain how to protect or unprotect an Excel workbook with macros, you must know how to create Macros. The procedure is very simple and will explain step by step so that you can perform without any problem and so can set up a Excel file for reading only.

To start you open the program Excel and position yourself on the Visual Basic Editor. You can view it at the top of the screen, to your left. It is usually represented by VS.

 green Excel logo office

Here an important clarification. It depends on the version of Excel you have available, vary the path. If you do not have the VB button on the toolbar at the top of the screen, performs the following sequence: «File & gt; Options & gt; Developer/Programmer & gt; Virtual Basic «

From the editor automatically opens and you can use the menus and tools of the window. You must place the following command in a new module: ActiveSheet brackets and a password with which we wish to establish protection, you can also add the name or number of sheet if you want to specify, or you can proteger cells in Excel to be unmodified.

How it works and what Macros for Excel

A macro automates frequently perform functions to optimize time editing a workbook in Excel. Are created with Microsoft Virtual Basic and are designed by software developers.

With it we can put a password to an Excel file for other users they can modify, copy or print the data that are available within that document. In a way that can only be accessed with a read mode, unless you enter the password to enable editing.

The first thing to do is allow the VBA object model projects access with a password. To do this you are making the following sequence «File & gt; Options & gt; Trust Center & gt; Trust Center Settings & gt; Macros settings. There must check «Trust access to the VBA object model projects.

 advertising excel in office 360 ​​

In order to protect a book, you also tick the box « Enable all macros» . If the want to check out, you must tick instead «Disable all macros with or without notice».

How to protect an Excel workbook with macros in a few steps

To protect the structure of the book so that anyone can create or delete sheets unless you know the password, you must enter the following command in VBA:

[CodeSyntax lang = «vb» lines = ‘no’ container = «pre» blockstate = «expanded» doclinks = «0»]

And then be written as follows:

Sub Protect ()

On Error GoTo order

ActiveWorkbook.Protect ( «password protect»)


End Sub

If you want to instead protect the entire book need to type the following command

[CodeSyntax lang = «vb» lines = ‘no’ container = «pre»]

And then shall be established as follows

Sub Protect (name of the book or page)

On Error GoTo order

Dim sht As Worksheet

Application.ScreenUpdating = False

ActiveWorkbook.Protect ( «password to protect the book»)

For Each sht In ActiveWorkbook.Worksheets

If sht.Visible = True Then

sht.Protect ( «password to protect the book»)

End If



End Sub

As you can see we have added a few lines in which discriminate leaves we want to protect the Protect command and the name or number sheet.

How to unprotect an Excel workbook in a few steps

You can check out an Excel workbook or a spreadsheet of it in the same way you did to protect only that we will change some details of the commands.

To check out a spreadsheet will insert the following code:

ActiveSheet.Unprotect «password you used to protect ‘

If instead we want to do is check out an Excel workbook, we insert the following code:

ThisWorkbook.Unprotect «password you used to protect ‘

We hope this tutorial on protection and security in Excel you have served useful. If you have doubts leave them comments so we can help you solve it.

How useful did you find this content?

Click on a star to rate!

Average score 0 / 5. Counting of votes: 0

So far, no votes. Be the first to rate this content.

I'm sorry this content was not useful for you!

Let me improve this content!

Tell me, how can I improve this content?

You may also be interested in:

Deja un comentario