Restore Password Protection for an Excel VBA Workbook Macro
Question details
The user needs an Excel VBA macro to restore password-protected workbook protection after Solver runs.

- 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.
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.
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.
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.
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.
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.
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 worksheet protection separately when needed
Use this solution if Solver requires a worksheet to be unprotected or the missing password prompt concerns a sheet rather than workbook structure.
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.

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.




