I was reviewing someone’s workbook the other day way I saw some code similar to this:
Sheets(“Sheet1″).Unprotect Password:=”MeatFace”
PROCEDURE
Sheets(“Sheet1).Protect Password:=” MeatFace “
This code unprotects the sheet, runs a procedure, and then protects the sheet again. The reason this guy wrapped his procedure with Unprotect/Protect is because his procedure changes a worksheet in some way. And you cannot run a macro that changes a worksheet while that worksheet is protected.
Now, I’ll bet a bunch of you still wrap your procedures with Unprotect/Protect. So I’m here to tell you that although this technique does work, it does have a couple of drawbacks. First, if the code in the middle errors out, then your sheet won’t become protected again. Second, every procedure (that changes the worksheet) would need to be wrapped in your UnProtect/Protect statements. This obviously leads to redundant code which could be difficult to manage.
A better way is to protect your worksheet with the optional UserIntefaceOnly argument.
Place this code in the Workbook Open event:
When you set the UserInterfaceOnly argument to True, you tell Excel that the worksheet protection does not apply to macros. This will allow your macros to run without having to constantly protect and unprotect your worksheet.
Bonus Tip – Free of Charge
There are other arguments (similar to UserInterfaceOnly) that can help better define what happens when you protect your worksheet.
For example, if I wanted to protect my worksheet, but still allow my customers to Enable/Disable Autofilters, I would add the AllowFiltering argument:
Sub ProtectSheet()
Sheets(“Sheet1″).Protect _
Password:=”MyPassword”, _
AllowFiltering:=True
End Sub
Here is a list of the arguments you can leverage – painstakingly copied and pasted from Excel help.
| DrawingObjects | True to protect shapes. The default value is True. |
| Contents | True to protect contents. For a chart, this protects the entire chart. For a worksheet, this protects the locked cells. The default value is True. |
| Scenarios | True to protect scenarios. This argument is valid only for worksheets. The default value is True. |
| UserInterfaceOnly | True to protect the user interface, but not macros. If this argument is omitted, protection applies both to macros and to the user interface. |
| AllowFormattingCells | True allows the user to format any cell on a protected worksheet. The default value is False. |
| AllowFormattingColumns | True allows the user to format any column on a protected worksheet. The default value is False. |
| AllowFormattingRows | True allows the user to format any row on a protected. The default value is False. |
| AllowInsertingColumns | True allows the user to insert columns on the protected worksheet. The default value is False. |
| AllowInsertingRows | True allows the user to insert rows on the protected worksheet. The default value is False. |
| AllowInsertingHyperlinks | True allows the user to insert hyperlinks on the worksheet. The default value is False. |
| AllowDeletingColumns | True allows the user to delete columns on the protected worksheet, where every cell in the column to be deleted is unlocked. The default value is False. |
| AllowDeletingRows | True allows the user to delete rows on the protected worksheet, where every cell in the row to be deleted is unlocked. The default value is False. |
| AllowSorting | True allows the user to sort on the protected worksheet. Every cell in the sort range must be unlocked or unprotected. The default value is False. |
| AllowFiltering | True allows the user to set filters on the protected worksheet. Users can change filter criteria but can not enable or disable an auto filter. Users can set filters on an existing auto filter. The default value is False. |
| AllowUsingPivotTables | True allows the user to use pivot table reports on the protected worksheet. The default value is False. |
I have done the same thing this guy did. Great tip, will definitely incorporate it into my future projects.
In the interest of protection, is there a way to hide the formulas from displaying in the formula box or the cell?
The only problem comes when you need to refresh a query/table.
The userinterface option wont work and you are stuck with protect / unprotect
Mike, it’s probably worth mentioning that you need to run the userinterface:=true every time the workbook opens though, as it’s not persistent between sessions.
Great tip! I’ll be using this in future projects.
@karl: you can hide formulas by setting cells to ‘Hidden’ on the ‘Protection’ tab in ‘Format Cells’. They then are hidden as soon as you protect the sheet.
Karl, you can hide the formula bar entirely by placing the following in the same workbook_open sub:
Application.DisplayFormulaBar = False
Hi … a remarkable aspect of this property is its persistence EVEN AFTER the user unprotects the sheet; when they go back to protect it again, VBA continues to be able to make changes to the newly protected sheet. In other words, the user can toggle settings EXCEPT for UserInterfaceOnly, and the UserInterfaceOnly property does not get turned off when the protection is turned off, so it doesn’t get turned back on again when the user re-establishes protection manually. To me this is weird… deep, very useful, but weird.