VBA & Macro Problems

Stop Excel Password Prompts for Linked Workbooks with VBA

Rana GarciaRana Garcia Nov 14, 2026 868 views

Question details

The user needs to access a password-protected source workbook without an external-link password prompt appearing before VBA runs.

Product
Microsoft Excel
Device & OS
not provided
Scenario
A view workbook contains external links to a password-protected source workbook and uses a Workbook_Open macro.
Observed behavior
Excel requests the source workbook password before the view workbook's Workbook_Open event runs.
Before you start

Identify the view workbook and its linked source workbook, and make sure you have permission to access the source. Open the view workbook and enable macros only if you trust it.

Solution 1Recommended

Prevent automatic external-link updates when the view workbook opens

Use the view workbook's UpdateLinks property to stop Excel from updating links before Workbook_Open runs.

1
Open the VBA editor

In Excel, open the view workbook, then go to Developer > Visual Basic or press Alt+F11.

2
Select the workbook object

In the Project Explorer, expand the view workbook's VBA project and select ThisWorkbook. Press F4 to display the Properties window if it is hidden.

3
Change the link setting

In the Properties window, set UpdateLinks to 2 - xlUpdateLinksNever. Save the view workbook, close it, and reopen it to test whether the password prompt is suppressed.

4
Check calculation and results

In Excel, go to Formulas > Calculation Options and select Automatic if appropriate. Check the linked cells against the source; automatic calculation does not guarantee that external data has been refreshed.

Linked values may be cached: Disabling startup link updates can leave saved external-link values in place. Open the source workbook and update the links when current data is required.
Free Microsoft Office alternative

Work with Excel files in WPS Spreadsheet

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easier to move everyday spreadsheet work. The Excel-specific VBA link setting must still be managed in Excel.

Open and edit linked spreadsheet data in a familiar interfaceCompatible with common Microsoft Excel formats, including XLS and XLSXFree and lightweight for everyday office workMove between common Office documents with minimal disruption
microsoft office alternative - wps office

Frequently Asked Questions

Why does the password prompt appear before my Workbook_Open macro?

Excel may attempt to update external links while the view workbook is opening, before the Workbook_Open event runs. Set ThisWorkbook's UpdateLinks property to 2 - xlUpdateLinksNever to prevent that startup update.

Will automatic calculation always refresh the linked workbook's data?

No. Formulas > Calculation Options > Automatic recalculates formulas, but it does not guarantee a fresh external-link update. Open the source and update the link when you need to verify current values.

Can VBA open the source workbook with its password?

Yes. In the view workbook's Workbook_Open procedure, Workbooks.Open accepts Filename and Password arguments. Disable startup link updates first so the prompt does not appear before the code runs.

Is a password stored in a protected VBA project secure?

VBA project protection is not a strong security boundary. Avoid hard-coding passwords where possible and restrict access to the source workbook using appropriate permissions.