VBA Trick of the Week:: Protect Worksheet for User Actions Only

Posted: July 24, 2013 by Transformer in Excel, VBA
Tags: ,

We protect a worksheet so that it can not be modified and this protection applies to VBA operations and user actions both. Some VBA operations like pasting data cannot be done on a protected worksheet. Now to perform these operations on that sheet we need to unprotect it, do the operations and then protect it again.
e.g.

Sub EditSheet()

    ThisWorkbook.Worksheets("Sheet1").Unprotect Password:="1234"
    ' Code to perform some operations to edit the sheet
    ThisWorkbook.Worksheets("Sheet1").Protect Password:="1234"

End Sub

Although the above technique works but we need to wrap our code between Unprotect and Protect statements in every procedure that performs some operations on the protected sheet. So if we need to protect our worksheet from user actions only then there is a better way of doing it.
Use UserInterfaceOnly parameter while protecting the sheet:

ThisWorkbook.Worksheets("Sheet1").Protect Password:="1234", UserInterfaceOnly:=True

What this statement does is that it protects the sheet from the front-end but allows you to do VBA operations.

Important Note: If you execute the above line and try some VBA operations on the sheet then it will allow you to do the same but if you close your workbook and reopen it then you will find your sheet protected for VBA operations and user actions both. Each time that you open the workbook you need to specify that you want your sheet protected for user actions only. So it is good to write that protection code on workbook open event.

Private Sub Workbook_Open()

    ThisWorkbook.Worksheets("Sheet1").Protect Password:="1234", UserInterfaceOnly:=True

End Sub

There are some other parameters also that can be used to allow user to perform actions on protected worksheet.
e.g. If you want your sheet protected for all operations except cell formatting:

Private Sub Workbook_Open()

    ThisWorkbook.Worksheets("Sheet1").Protect Password:="1234", UserInterfaceOnly:=True, AllowFormattingCells:=True

End Sub

Similarly, we have AllowFiltering ,AllowSorting and AllowDeletingRows etc.

Advertisements
Comments
  1. snb says:

    So what’s the rationale of protecting a worksheet for a user ?
    If you want the user to be able to modify data, employ a userform, not a worksheet as entryform

  2. Craig says:

    Great tip! I’ve been struggling with having to continually lock and unlock worksheets in order to perform VBA operations and have been unable to keep the worksheets protected if the user cancels in the middle of the process. This solves that issue completely.

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s