Protect Excel Worksheet as UserInterfaceOnly with VBA

When you protect an Excel worksheet as UserInterfaceOnly, the sheet stays locked for people but stays open for your VBA macros. That solves two everyday problems. First, a macro that writes to a protected sheet normally stops with a runtime error. Second, users cannot expand or collapse grouped rows or columns on a protected sheet – Excel just says you cannot use this command on a protected sheet. In this tutorial you will see both problems, fix them with one line of VBA each, and then make the fix permanent with a Workbook_Open event. The practice file is free to download.

Protect Excel worksheet as UserInterfaceOnly: Employee Sales sheet with grouped daily columns and Jan to May Total columns that expand and collapse while protected — The Employee Sales sheet: daily columns are grouped by month, and the + / – buttons keep working even though the sheet is protected.

⬇️ Download the Practice File
ZIP with one macro-enabled workbook · .xlsm (enable macros) · about 31 KB

Table of Contents

Watch the Video Tutorial

In the video PK shows the protected-sheet error, writes the UserInterfaceOnly macro step by step, turns on EnableOutlining for grouped columns and moves the code into Workbook_Open so it survives a restart.

What You Will Learn

Why a macro fails on a protected sheet and how UserInterfaceOnly:=True fixes it.
How to let users expand and collapse groups on a protected sheet with EnableOutlining.
Why the setting disappears when you close the file, and how Workbook_Open brings it back automatically.
The full list of arguments of the Protect method.

What It Means to Protect an Excel Worksheet as UserInterfaceOnly

UserInterfaceOnly is an optional argument of the Worksheet.Protect method in VBA. By default it is False, which means protection blocks everybody – you at the keyboard and your macros. When you set it to True, protection applies only to the user interface:

Users still cannot type in locked cells or change formatting by hand.
VBA code can write values, change formats and do other work on the sheet without unprotecting it first.

This works with or without a password.

The Practice File

The workbook has two sheets, both protected:

Sheet1 – an empty grey area used to test writing a value with VBA (the macro writes into E4).
Employee Sales – EMP ID and EMP Name for 22 employees in columns A and B, one column per day from 1-Jan-2019, and a total column after each month: Jan Total (AH), Feb Total (BK), Mar Total (CQ), Apr Total (DV) and May Total (FB). Each month total is a simple SUM, for example =SUM(C2:AG2) for January.

The daily columns of each month are grouped with Data › Group, so the + and – buttons above the sheet show or hide a whole month. On a protected sheet those buttons stop working – that is the problem we will fix.

Part 1 – Let a Macro Write to a Protected Sheet

Step 1: See the problem

Protect Sheet1 from Review › Protect Sheet and try typing in E4 – Excel refuses, which is what you want. Now open the VBA editor (Developer › Visual Basic or Alt+F11), insert a module and write a macro that protects the sheet and then writes to E4:

Sub Protect_Sheet()

    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")

    sh.Protect "1234"
    sh.Range("E4").Value = "WWW.PK-AnExcelExpert.Com"

End Sub

Run it and the macro stops with a runtime error on the line that writes to E4, because a normal protected sheet blocks VBA too.

Step 2: Add UserInterfaceOnly:=True

Change the Protect line so it sets the UserInterfaceOnly argument. This is the macro from the practice file (Module1):

Sub Protect_Sheet()

    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")

    sh.Protect "1234", userinterfaceonly:=True
    sh.Range("E4").Value = "WWW.PK-AnExcelExpert.Com"

End Sub

Run it again. There is no error and E4 now shows WWW.PK-AnExcelExpert.Com, yet if you click E4 and try to type, the sheet is still protected.

How the code works

Set sh = ThisWorkbook.Sheets("Sheet1") points the variable at the sheet, so the rest of the code is short and readable.
sh.Protect "1234", userinterfaceonly:=True protects the sheet with the password 1234 and tells Excel to block only manual changes. userinterfaceonly:=True is a named argument, so you do not need to count commas.
The alternative is to pass it by position: UserInterfaceOnly is the fifth argument, after Password, DrawingObjects, Contents and Scenarios, so sh.Protect "1234", , , , True does the same thing. The named version is much easier to read.
sh.Range("E4").Value = ... now runs on the protected sheet without any Unprotect.

Many people instead write sh.Unprotect "1234", run their code and then call sh.Protect "1234" again. That works, but if the macro stops halfway the sheet is left unprotected. UserInterfaceOnly avoids that risk.

All arguments of the Protect method

When you type sh.Protect and a space, the VBA editor lists every argument. All are optional:

Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables

The Allow… arguments give users specific rights on a protected sheet, such as sorting or formatting rows, in the same way as the check boxes of the Protect Sheet dialog.

Part 2 – Expand and Collapse Groups on a Protected Sheet

Step 3: Turn on EnableOutlining

Protect the Employee Sales sheet with UserInterfaceOnly and set its EnableOutlining property to True:

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Employee Sales")

sh.Protect "1234", userinterfaceonly:=True
sh.EnableOutlining = True

Run it, then click the + button above Feb Total. The February days open; click – and they close again. Try typing in any cell and Excel still says the sheet is protected. Users can browse the months, but they cannot change the numbers.

EnableOutlining only works together with UserInterfaceOnly protection. With normal protection the outline symbols stay locked.

Part 3 – Make It Permanent with Workbook_Open

Step 4: Understand why it stops working

Save the file, close it and open it again. The sheet is still protected, but the + and – buttons are blocked again and the macro cannot write any more. The reason: Excel saves the protection and the password, but not the UserInterfaceOnly flag or the EnableOutlining setting. They last only while the workbook is open, so the code has to run every time the file opens.

Step 5: Put the code in the Workbook_Open event

In the VBA editor double-click ThisWorkbook, choose Workbook in the left drop-down and Open in the right one. Excel creates the empty Workbook_Open procedure. Put your code inside it. This is the code from the practice file:

Private Sub Workbook_Open()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Employee Sales")

sh.Protect "1234", userinterfaceonly:=True
sh.EnableOutlining = True

End Sub

Save the workbook as .xlsm. From now on, every time the file opens, Workbook_Open re-protects Employee Sales as UserInterfaceOnly and switches outlining back on, so the groups and your macros keep working.

Common Errors and Fixes

Runtime error when the macro writes to the sheet. The sheet was protected without UserInterfaceOnly, or the file was reopened and the setting was lost. Run the Protect line with userinterfaceonly:=True again, ideally from Workbook_Open.
Workbook_Open does not run. The code is in a normal module instead of ThisWorkbook, the file was saved as .xlsx (which removes macros), or macros are disabled. Enable content when Excel shows the security bar. (Addition: general Excel guidance.)
The + and – buttons still do not work. EnableOutlining was set without UserInterfaceOnly protection, or it was set on a different sheet. Check the sheet name inside Sheets("...").
The sheet name was changed. Sheets("Employee Sales") fails with a subscript out of range error if the tab is renamed. Update the name in the code. (Addition.)

Tips

Change the sample password 1234 to your own before you use this in a real file, and lock the VBA project (Tools › VBAProject Properties › Protection) so users cannot read the password in the code. (Addition.)
To protect every sheet the same way, loop through ThisWorkbook.Worksheets inside Workbook_Open and call Protect on each one. (Addition.)
Sheet protection is meant to prevent accidental changes, not to secure confidential data. (Addition.)
Microsoft documents every argument in Worksheet.Protect method (Excel).

Want It Ready-Made?

If you want ready-to-use macro tools instead of writing them, the Excel VBA Tools & Utilities Pack on NextGenTemplates.com bundles eight Excel VBA templates, or you can browse all Excel VBA tools.

Frequently Asked Questions

What does UserInterfaceOnly do in Excel VBA?

It is an argument of the Protect method. When it is True, the sheet is protected against manual changes only, so VBA macros can still edit cells and formats without unprotecting the sheet.

Why does UserInterfaceOnly stop working after I reopen the file?

Excel does not save the UserInterfaceOnly flag with the workbook. Run the Protect line again every time the file opens by putting it in the Workbook_Open event, as shown in Part 3.

How can users expand and collapse groups on a protected sheet?

Protect the sheet with UserInterfaceOnly set to True and then set the EnableOutlining property of that sheet to True. Users can then use the plus and minus outline buttons while the cells stay locked.

Do I need a password to use UserInterfaceOnly?

No. The password is optional. The setting works the same with or without one, but a password stops users from simply unprotecting the sheet from the Review tab.

Is UserInterfaceOnly better than Unprotect and Protect in every macro?

For most files, yes. It keeps the sheet protected the whole time, so an error in the middle of a macro can never leave the sheet unprotected.

About the Author

PK is a Microsoft Excel expert and trainer and the founder of PK: An Excel Expert and NextGenTemplates.com. He has been teaching Excel, VBA, Power Query and Power BI since 2016 on his YouTube channel.

Conclusion

To protect an Excel worksheet as UserInterfaceOnly, add userinterfaceonly:=True to the Protect line, add EnableOutlining = True if users need the group buttons, and run it all from Workbook_Open so it is applied every time the file opens. Your sheet stays locked for users, your macros keep working and the monthly groups stay easy to browse. Download the practice file and try it on the Employee Sales sheet.