error message keeps showing on opening file

YifanWang3 

The error message you're encountering in Excel—"cannot find #REF!#REF!, which has been assigned to run each time [xxx.xls] is opened. continuing....."—indicates that Excel is trying to reference something (like a macro, link, or formula) that no longer exists or is incorrectly defined. This issue can arise due to various reasons such as broken links, missing macros, or corrupted references.

Here’s how you can troubleshoot and resolve this issue:

Step 1: Check for Missing Macros or References

1. Open Excel in Safe Mode (this disables macros temporarily):
• Hold down the Ctrl key while opening the Excel file.
• This ensures that macros don’t run automatically when the workbook opens.
2. Check for Macros:
• Go to the Developer tab (if it’s not enabled, you can enable it through File > Options > Customize Ribbon).
• Click on Macros to see if there are any macros listed that might be causing the problem.
• If you see any macros that are linked to missing files or references, either edit or delete them.
3. Check Workbook and Worksheet Events:
• In the Developer tab, click on Visual Basic to open the VBA editor.
• In the Project Explorer window, look for ThisWorkbook and any worksheet objects (e.g., Sheet1, Sheet2, etc.).
• Double-click on ThisWorkbook and each sheet object to see if there are any event-driven macros (e.g., Workbook_Open, Workbook_SheetChange, etc.) that reference something that doesn’t exist.
• If you find references to missing items, either update or remove the code.

Step 2: Search for Invalid Links

1. Check for External Links:
• Go to the Data tab.
• Click on Edit Links (if it’s available). This will show any external files that your workbook is linked to.
• If any of the links are broken, you can either update the link to a correct file or break the link.
2. Check for Named Ranges:
• Go to Formulas > Name Manager.
• Look for any named ranges that might reference #REF! or invalid cells.
• If you find any, either correct the references or delete the named ranges.

Step 3: Check for Add-ins

1. Check for Active Add-ins:
• Go to File > Options > Add-ins.
• At the bottom of the window, where it says Manage, select Excel Add-ins and click Go.
• Deselect any add-ins that might be causing the issue and click OK.

Step 4: Inspect and Clean Up Workbook

1. Check Formulas:
• Use the Find and Replace tool (Ctrl + F) and search for #REF!.
• Replace any #REF! errors with correct references or formulas.
2. Inspect Hidden Sheets:
• Right-click on any worksheet tab and choose Unhide to see if there are any hidden sheets.
• Unhide and inspect these sheets for any broken references.

Step 5: Save and Test

1. Save As: Save the workbook with a new name (e.g., xxx_cleaned.xls) to ensure that you don’t overwrite your original file.
2. Close and Reopen the cleaned workbook to see if the error message still appears.

Step 6: Final Cleanup (If Necessary)

• If the error persists, you may need to recreate the workbook by copying your data and content into a new workbook and setting up any necessary references and macros from scratch.

By following these steps, you should be able to identify and correct the source of the #REF! error that is causing the issue when you open the workbook. The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.