VBA & Macro Problems

Restore Password Protection for an Excel VBA Workbook Macro

John WilsonJohn Wilson Nov 20, 2026 869 views

Question details

The user needs an Excel VBA macro to restore password-protected workbook protection after Solver runs.

Restore Password Protection for an Excel VBA Workbook Macro
Product
Microsoft Excel
Device & OS
not provided
Scenario
A macro unprotects a workbook for Solver and then protects it again.
Observed behavior
After the macro runs, manually unprotecting the workbook does not prompt for a password, unlike protection set manually.
Before you start

Identify the workbook and worksheets that the macro unprotects, and locate the Protect and Unprotect calls around Solver. Make sure you know the intended password before testing the macro.

Solution 1Recommended

Restore workbook structure protection with the intended password

Use this approach when the macro should protect the workbook structure after Solver finishes.

Workbook structure protection and worksheet protection are separate. Protecting one does not automatically protect the other.

1
Open the VBA procedure

In Excel, open Developer > Visual Basic and find the procedure that runs Solver. Identify the workbook object used by both the Unprotect and Protect calls.

2
Check both password arguments

Pass the same intended password to both calls. For example, if the macro belongs to the workbook being protected, use ThisWorkbook.Unprotect Password:="your-password" before Solver and ThisWorkbook.Protect Password:="your-password", Structure:=True after Solver. Replace the example password with the intended value.

3
Check the execution path

Use the VBA editor to step through the procedure and confirm that execution reaches the Protect call after Solver. If Solver can exit or raise an error, route those paths through a cleanup section that restores protection.

4
Verify the result

After running the macro, open Review > Protect Workbook and try to remove protection. Confirm that Excel asks for the intended password. In the VBA editor's Immediate window, you can also check ?ThisWorkbook.ProtectStructure; True confirms structure protection, but not that a password was set.

Restore workbook structure protection with the intended password
Choose the correct workbook: ThisWorkbook refers to the workbook containing the VBA code. If the macro protects a different workbook, use a reference to that workbook consistently instead.
Free Microsoft Office alternative

Work with spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface for opening and editing Office files. It can help you migrate everyday spreadsheet work, although it does not fix Excel's VBA protection behavior and macro or Solver compatibility may vary.

Use a familiar spreadsheet interface for everyday workbook editingOpen and edit Microsoft Excel formats such as XLSXWork with spreadsheet protection features where supportedUse a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Does a VBA macro have to enter the password twice when protecting a workbook?

No. The Workbook.Protect method takes the password once through its Password argument. Excel's manual confirmation prompt is not a second VBA argument.

Why does Excel unprotect the workbook without asking for a password?

The workbook may have been protected without a password, or the macro may have protected a different object. Check the Password argument and workbook reference in the Protect call, then test Review > Protect Workbook.

How can I tell whether workbook structure protection is on?

In the VBA editor's Immediate window, enter ?ThisWorkbook.ProtectStructure. A True result means the structure is protected, but it does not confirm that a password was supplied.

Does protecting the workbook also protect its worksheets?

No. Workbook structure protection and worksheet protection are separate. Use Workbook.Protect for the structure and Worksheet.Protect for each sheet that needs protection.

What if Solver stops before the macro restores protection?

Add error handling that routes execution to a cleanup section containing the appropriate Protect call. Test both normal completion and error paths in the VBA editor.