Worksheets("xx").Activate - breaks macro

Brass Contributor

I have a file with a well-established macro that I wrote years ago. It's a file I reset (with macro button) and then SAVE AS for the new date. I use it daily. For some reason, today the macro throws a run-time error -

 

Automation error, invalid forward reference, or reference to uncompiled type.

 

When I click on debug, it's highlighting [ Worksheets("Tally_Summary").Activate ].

I never change the sheet names, so it's not that. 

It's not already on that sheet, so it's not that.

I've tried closing the file, restarting my computer, opening an older version (previous date) and nothing seems to work. 

 

Quick update: just noticed something that happened yesterday as well. 

Even though it is a macro-enabled workbook, clicking on the macro dialog box shows no available macros. So, THAT is probably the main issue. I have another file that did this yesterday and I couldn't save the file at all. 

6 Replies

@RandomPanda 

It seems like there might be an issue with the macro-enabled workbook itself or with Excel's macro settings. Here are some steps you can try to troubleshoot and resolve the issue:

  1. Enable Macros: First, ensure that macros are enabled in Excel. Sometimes, macros can be disabled due to security settings. Go to "File" > "Options" > "Trust Center" > "Trust Center Settings" > "Macro Settings," and ensure that "Enable all macros" or "Enable all macros (not recommended; potentially dangerous code can run)" is selected.
  2. Check Macro Security Level: Verify that the macro security level is set to a level that allows macros to run without restrictions. If it's set too high, it might prevent macros from running.
  3. Check Antivirus or Security Software: Sometimes, antivirus or security software can interfere with macros. Temporarily disable any antivirus or security software and see if the issue persists.
  4. Inspect the Workbook for Corruptions: Open the workbook and go to "File" > "Info" > "Check for Issues" > "Inspect Document." This will check for any hidden information or content that may be causing issues.
  5. Try Opening the Workbook in Safe Mode: Open Excel in safe mode by holding down the Ctrl key while opening Excel. This will prevent any add-ins or customizations from loading. Open the workbook in safe mode and see if the issue persists.
  6. Repair Office Installation: If the issue continues, try repairing the Office installation. Go to "Control Panel" > "Programs" > "Programs and Features," select Microsoft Office, and choose "Change" or "Repair."
  7. Create a New Workbook: If none of the above steps resolve the issue, try creating a new workbook and copying the contents of the problematic workbook into the new one. This may help if the issue is specific to the workbook itself. The text was created with the help of AI.

If the problem still persists after trying these steps, provide more detail.

Explanation link for more Details: Welcome to your Excel discussion space!

 

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.

@NikolinoDE 

Thank you for the suggestions. I ended up having to copy the data to a file I saved the previous day (Friday) and everything worked ok. I saved over the file that had the issues. But, I opened up the other file that was corrupted (somehow) and tried a couple of your suggestions.

First, I made sure macros were enabled. That didn't change anything.

Second, I inspected the workbook. It showed "macros or vba code found". But, again, I pulled up "Show macros" and nothing was there. I tried editing in VBA editor and found nothing as well. 

Lastly, I just tried to save/close and I get this error:

"Errors were detected while saving "<filename>". Microsoft Excel may be able to save the file by removing or repairing some features. To make repairs in a new file, click Continue. To cancel saving the file, click Cancel"

When I click continue, I try to save as a different filename - that doesn't work. Then it doesn't want to close (clicking the "x").

When I finally can close it (I couldn't repeat the error today) it says something like - "a version was saved to Apps/Roaming/Excel....(generic default folder)." But, even that file doesn't include any of the changes I made (data added during the session) and has removed pretty much all formulas. 

 

I guess, I was able to get around the issues by recreating the files from an earlier version. But, I would love to be able to figure out what caused it to crash like that - so I can avoid it in the future. And, I didn't click on any random links from a Nigerian prince, so I'm pretty sure that's not it. :)

 

The only saving grace is that I have a template for this other file since it is a weekly file. I created the new week file from the template without issues.

 

@RandomPanda 

It sounds like you've encountered a complex issue that may involve various factors. Here are a few additional steps you can take to troubleshoot and potentially prevent similar issues in the future:

  1. Review Recent Changes: Reflect on any recent changes you made to the workbook or your system. Even seemingly minor adjustments could potentially trigger unexpected behavior. This could include updates to Excel or your operating system, changes to settings or configurations, or even updates to other software that might interact with Excel.
  2. Examine Recent Updates: Check if any recent updates were applied to Excel or your operating system around the time the issue started occurring. Sometimes updates can introduce bugs or compatibility issues that affect the functioning of macros or other features in Excel.
  3. Investigate Add-Ins: Review any add-ins you have installed in Excel. Sometimes conflicts between add-ins or issues with specific add-ins can cause unexpected behavior. Try disabling any recently installed add-ins or ones that you suspect might be causing conflicts and see if the issue persists.
  4. Consider File Size and Complexity: Large file sizes or complex formulas and calculations can sometimes strain Excel's capabilities, leading to unexpected errors or crashes. If your workbook has grown significantly in size or complexity over time, consider simplifying or optimizing it where possible.
  5. Check for External Links or References: Ensure that your workbook does not contain any external links or references to other files or locations that might have been inadvertently broken or corrupted. These can sometimes cause issues with saving or closing the workbook.
  6. Monitor System Resources: Keep an eye on your system resources (such as memory and CPU usage) while working with Excel, especially when running macros or performing intensive operations. Insufficient resources can sometimes lead to crashes or errors.
  7. Backup Regularly: Make it a habit to regularly backup your Excel workbooks, especially ones that contain important or sensitive data. Having backups can provide a safety net in case of unexpected issues or data loss. The text was revised with the AI.

By systematically investigating these potential factors, you may be able to identify the root cause of the issue you encountered and take steps to prevent similar problems in the future.

If the issue persists or if you require further assistance, please provide more information. Information about your Excel version and about the other PC. Operating system of both PCs (Mac, Windows, Linux, etc.), storage medium (hard drive, Onedrive, Sharepoint, Dropbox, etc.), file extension (.xlsm, xls, xlsx, etc.).

What ending did the file have at the beginning and what was the ending after saving?

Many small details help to get a better picture of the issue.

 

My answers are voluntary and without guarantee!

 

Hope this will help you

@RandomPanda There's nothing you did to cause this. It is 100% the result of bad updates from Microsoft and has been affecting practically everyone since mid-December. Although Microsoft has claimed to resolve this issue in their February update, the fact that you and other people are still experiencing these issues suggests otherwise. There's a really good discussion thread that got a lot of attention back in December, where people have continued to share the ways in which they resolved the problem. The suggestion you might find most applicable for your situation was made by MKirsch, and can be viewed here: https://techcommunity.microsoft.com/t5/excel/excel-macros-disappear-from-xlsm-file/m-p/4040066/highl... 

 

By all means read through the entire thread, though, as there are other suggestions that indicate uninstalling the 64-bit version of Excel and reinstalling the 32-bit version will give you less grief moving forward (I have not tested this yet to verify it).

Errors were detected while saving "<filename>". Microsoft Excel may be able to save the file by removing or repairing some featur

Sometimes antivirus protect i.g. defender will cause this problem and system region setting.
Found this that worked on that thread. THANK YOU!
--------------------------------------
CO_Data_Wrangler replied to RBinNC
‎Feb 15 2024 10:58 AM

@RBinNC I encountered the same issue this morning.
Try this: open the VBA Developer window for the workbook and go to:
Tools --> References
and be sure the "AccessibilityCplAdmin 1.0 Type Library" is checked.
For whatever reason, that seemed to clear up the error for me.
Hope it works for y'all, too.
-----------------------