Excel: Protect all sheets automatically with VBA

Let me show you how easy it is to expand your Excel skills and productivity with just a few lines of code. You might have been using excel for years and never even considered that you can write your own code for excel.

This post started by following the discussion on uservoice for a specific uservoice item regarding whether or not to show a lock icon on sheet tabs. Quite a few people were complaining that they needed an easy way to see if all sheets were locked.

But there is an even better solution than relying on the visual confirmation, because that still involves going through all the tabs, and you could have many. Why not simply add a piece of code that protects all cells when we close the workbook?

If you are new to Excel VBA (Visual Basic for Applications) I have made these simple steps to show you how it works. Trust me, even if you have no coding experience at all, you can do this. You can either keep on reading, or just watch the video below. Instructions are the same.

So start by opening the workbook you want to protect. Hit Alt-F11 which will open up the Visual Basic Editor (VBE for short), which is going to look like this:

All office programs (Outlook, Word, Excel, Access, Powerpoint…) support VBA. Excel has a whole series of EVENTS, which we can tie into. In this case, we want to take an action whenever the workbook is closing. So select ThisWorkbook on the left hand side (doubleclick), then use the dropdown top left to select Workbook:

Now excel will choose the first event and autopopulate a code stump in your window for the open event, which is not what we want in this case.

So we go the second dropdown, top right, and select BeforeClose

Again, excel makes a small code stump for us:

Now, anything we write between the those lines, will be executed each time the workbook is about to close. So type in the following (or copy paste it)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Dim myWorksheet As Worksheet
   For Each myWorksheet In ThisWorkbook.Worksheets
      myWorksheet.Protect
   Next
End Sub

Voila, now each time your workbook closes, it will automatically protect all your worksheets. It’s that simple.

There is one thing to be aware of. Next time you save, excel will likely show you a prompt like this:

That is because excel differentiates whether its a workbook with code, or a workbook without code. So we need to save it in a format that supports code, so click no, and then in the Save As Type box, select Macro-Enabled Workbook

Join me for the next post where we take this example further, and look at how we can:

• Exclude certain sheets from locking
• Include a password
• Only activate the code when you are the user

If you want to learn more tips on tricks on office and VBA, don’t forget to subscribe.