Forum Discussion
Worksheets("xx").Activate - breaks macro
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 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/highlight/true#M218598
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).
- 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.
-----------------------
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:
- 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.
- 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.
- 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.
- 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.
- 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.
- 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."
- 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.
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.
- 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.
