VBA & Macro Problems

Hide and Unhide Rows on a Protected Excel Worksheet with VBA

Elise WilliamsElise Williams Nov 18, 2026 868 views

Question details

Allow VBA to hide and unhide rows while keeping an Excel worksheet protected for users.

Hide and Unhide Rows on a Protected Excel Worksheet with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
Rows are shown or hidden according to cell values, but the worksheet must remain protected against normal user edits.
Observed behavior
The row visibility macro works until worksheet protection is enabled.
Before you start

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm), and identify the protected sheet name and its protection password, if any. Make sure macros are enabled when you open the workbook.

Solution 1Recommended

Protect the worksheet for users while allowing VBA changes

Use UserInterfaceOnly protection so your existing macro can change row visibility without leaving the worksheet unprotected.

UserInterfaceOnly protects the worksheet from normal user edits while allowing VBA to make changes. If the rows are controlled by an outline, EnableOutlining also lets users operate the outline controls on the protected sheet.

Excel does not reliably retain UserInterfaceOnly protection after the workbook is reopened, so apply it in the Workbook_Open event each time.

1
Open the workbook code

In Excel, open the Visual Basic Editor with Alt+F11. In Project Explorer, double-click ThisWorkbook.

2
Add the protection setup

Paste this event procedure into ThisWorkbook, replacing Sheet1 and the password with your own values. Use an empty string if the sheet has no password: Private Sub Workbook_Open() Dim wsh As Worksheet Set wsh = ThisWorkbook.Worksheets("Sheet1") wsh.Unprotect Password:="your-password" wsh.EnableOutlining = True wsh.Protect Password:="your-password", UserInterfaceOnly:=True End Sub

3
Reopen and test

Save the .xlsm file, close and reopen it, and enable macros if prompted. Change the cell values that drive your existing row-visibility macro and confirm that rows update while the sheet remains protected.

Protect the worksheet for users while allowing VBA changes
Outline and password considerations: EnableOutlining is needed for outline controls, not for every Rows.Hidden macro. Enabling it lets users expand or collapse outlines. A password stored in VBA code is not secure encryption.
Free Microsoft Office alternative

Work with protected spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and compatibility with common Excel formats, making it easy to move everyday workbook tasks to WPS Spreadsheets. Excel VBA behavior and macro support can vary, so test this protection workflow before migrating it.

Open and edit workbooks in familiar Excel formats such as XLSXUse worksheet protection tools for everyday spreadsheet editingNavigate a familiar interface for a smoother transition from Microsoft OfficeWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does my row-hiding macro stop when I protect the sheet?

Standard worksheet protection can block changes to row visibility. Protect the sheet in VBA with UserInterfaceOnly:=True so macros can make changes while normal user edits remain restricted.

Do I need EnableOutlining to hide rows with VBA?

Not necessarily. EnableOutlining is relevant when users need to operate grouped row outline controls on a protected sheet. A macro that directly sets Rows.Hidden can use UserInterfaceOnly protection without it.

Why does the macro stop working again after I reopen the workbook?

UserInterfaceOnly protection may not persist after reopening. Put the protection setup in the Workbook_Open procedure in ThisWorkbook, save as .xlsm, and enable macros when prompted.

Does AllowFormattingCells let users hide and unhide rows?

AllowFormattingCells permits cell formatting; it is not required for VBA row visibility changes when UserInterfaceOnly is enabled. Add protection permissions only if users need those additional actions.