Many Excel macros run on protected worksheets. If you use the little piece of VBA code in this article they will run a lot faster.
Let me give you an example. I just finished a very robust Return on Investment calculator that has seven sheets of data entry, revenue forecasts, and output reports. It uses two different methods of calculating licensing fees, has five lines of business, has customer revenue retention calculations for five years on each line of business, and creates three pages of analysis. And one more thing! It has a drop-down to change currencies between eight different countries and checkboxes to show or hide multiple charts and line items in the reports.
Using normal VBA macro programming there is an eight second delay with each click of a check box. Imagine – one click, wait eight seconds, then another click. That would drive users mad.
Using the snippet of code described here the check box response is instant!
Don’t Use Protect and Unprotect in Excel VBA
Most Excel macros that run on protected worksheets make worksheet changes by unprotecting the worksheet(s), making changes, then re-protecting the worksheets. This method has problems.
- All the unprotecting and re-protecting of worksheets makes the macro run very slowly.
- If the VBA is not written with an On Error routine, then the worksheet can be left unprotected if the macro breaks.
- Often macros using Protect are written so the password is viewable in the code. Knowledgeable users can open the VBA Editor and see the password. (In reality, earlier versions of Excel were very easy to break into with inexpensive file breaking programs available over the web.)
Using Protect with the UserInterfaceOnly argument eliminates the first two problems. (The visible password has an easy solution also, but it’s not covered in this article.)
The Visual Basic code to unprotect sheets, make changes, and then re-protect worksheets looks like this,
Sub ROICalc() Sheets(“sheetname”).Unprotect Password:=”secretpw” Code to make changes goes here Sheets(“sheetname”).Protect Password:=”secretpw” End Sub
But, if you have many sheets to unprotect and re-protect this code sucks up computer processor speed and could leave your worksheet vulnerable.
Use Protect UserInterFaceOnly in Excel VBA to Quickly Change Protected Worksheets
If you want VBA code to make changes to protected sheets while they remain protected against users, then use this,
VBA code can change the protected worksheets without removing the worksheet protection. This leaves them protected even if the macro breaks and it significantly increases speed.
UserInterfaceOnly is an optional argument that by default is False. That means that in most VBA code you won’t see this argument used because it defaults to False so that VBA can only change unprotected worksheets.
By setting UserInterfaceOnly to TRUE it enables VBA to change a worksheet while keeping it protected against changes made through the user interface.
The code to use UserInterfaceOnly looks like this,
Private Sub Workbook_Open() Sheets(“sheetname”).Protect Password:="Secret", UserInterFaceOnly:=True Sheets(“sheetname”).Protect Password:="Secret", UserInterFaceOnly:=True 'Repeat with the name and password of additional sheets to be manipulated by VBA. End Sub
If you have multiple sheets that all use the same password, then you can use code like this,
Private Sub Workbook_Open() Dim wSheetName As Worksheet For Each wSheetName In Worksheets wSheetName.Protect Password:="Secret", UserInterFaceOnly:=True Next wSheetname End Sub
Warning! If you save, then reopen the workbook that contains this wonderful piece of code, all the worksheets will remain protected and your macro can’t change them. The code must run each time you open the workbook.
To insure the code always runs when the workbook opens, put this code in the Private Module of ThisWorkbook. ThisWorkbook is located at the top left in the Visual Basic Editor.
Using the Workbook_Open Event (see the subroutine name above) insures the code runs when the workbook opens, but it must be put in the ThisWorkbook module.