Forum Discussion

XiongYang's avatar
XiongYang
Copper Contributor
Jan 28, 2024

Macro-Enabled Workbooks not working.

Hello all,
I've recently started to see more issues with macro enabled workbooks within our work unit. We are on

Microsoft® Excel® for Microsoft 365 MSO (Version 2311 Build 16.0.17029.20140) 64-bit.

I've been seeing an increase in .xlsm files where the active form controls do not work (when users click nothing happens). When you go into the VBA project, the code does not appear. I then have to disable macros, save the workbook. Re-open the file, re-anable the macros again, and then it is fixed. However, the workbooks will occasionally revert back to having the same issue with the VBA code "disappearing". I have to currently deploy the same fix each time. . .

Another problem is workbooks not opening properly. Some workbooks have save methods within the Workbook Object code. Historically, it's never been a problem. The code does not seem very complex, as it just refreshes all connections upon opening the file, or saving a copy when the workbook is opened. I often have to apply a file level repair to resolve the issue.

All these problems are happening sporadically, and to multiple files. Any ideas any recent updates causing this many issues??

2 Replies

  • XiongYang 

    Solution No. 1

    I understand your frustrations with the recent issues affecting macro-enabled workbooks in your organization. Let's delve deeper into potential causes and solutions:

    1. Potential Causes:

    • Version 2311 bugs: While unlikely, some reported cases mention VBA code disappearing in specific versions.
    • File corruption: Network issues or unexpected shutdowns can corrupt files, including VBA code.
    • Anti-virus interference: Overly aggressive antivirus software might block macros or corrupt files.
    • Third-party add-ins: Incompatible or outdated add-ins can cause conflicts with macros.
    • Macro security settings: Strict settings might disable macros or require manual enabling.
    • User permissions: Users might lack necessary permissions to run macros.

    2. Troubleshooting Steps:

    • Isolate the Issue:
      • Try opening affected workbooks on a different computer/network to see if the problems persist.
      • Create a new, minimal workbook with simple macros to test if the issue replicates.
    • Check Updates:
      • Ensure you have the latest Microsoft 365 updates.
      • Consider temporarily reverting to a previous version if the issue started after a specific update.
    • Verify File Integrity:
      • Use "File" > "Info" > "Check for Issues" to repair potential file corruption.
      • Consider specialized data recovery tools for severe cases.
    • Analyze Add-Ins:
      • Temporarily disable add-ins one by one to identify potential conflicts.
      • Update or remove problematic add-ins.
    • Adjust Macro Security Settings:
      • Check settings for trusted locations and specific files.
      • Adjust cautiously to balance security and functionality.
    • Review User Permissions:
      • Ensure users have appropriate permissions to run macros.
    • Consult Microsoft Support:
      • Report the issue with detailed information if troubleshooting fails.

    3. Additional Tips:

    • Regular Backups: Back up important workbooks frequently.
    • Version Control: Consider a version control system for managing changes and rollbacks.
    • User Reporting: Encourage users to report issues promptly with detailed descriptions.
    • Security Assessment: Periodically review macro security settings to ensure balance.

    By implementing these steps and considering the provided tips, you can effectively diagnose and address the macro-enabled workbook issues in your organization. Remember, prioritizing critical issues and keeping users informed is crucial.

    Further Suggestions:

    • Gather more information: Collect specific file sizes, error messages, and user actions leading to issues.
    • Community forums: Seek solutions and experiences from other Microsoft 365 users.
    • Microsoft documentation: Consult official resources for troubleshooting and configuration guides.

    I hope this comprehensive response helps you resolve the macro-enabled workbook issues effectively!

     

    Solution No. 2

    1. Problem Statement:

    • Macro-enabled workbooks (.xlsm) in your organization are experiencing several issues:
      • Form controls not working (no response on click).
      • VBA code disappearing from the project pane.
      • Workbooks failing to open properly, requiring file repair.
      • These issues seem to be intermittent and affect multiple files.

    2. Possible Causes:

    • Recent Microsoft 365 updates (version 2311) might have introduced compatibility issues with macros.
    • Corrupted files due to network issues or unexpected shutdowns.
    • Anti-virus software interfering with macro execution.
    • Third-party add-ins causing conflicts.
    • User permissions and macro security settings not configured correctly.

    3. Action Items:

    • Isolate the Issue:
      • Test the affected workbooks on a different computer/network to see if the issues persist.
      • Create a new, minimal workbook with basic macros to see if the problem replicates.
    • Check Updates:
      • Ensure you have the latest Microsoft 365 updates installed.
      • Consider temporarily reverting to an earlier version if the issue started after a specific update.
    • Review File Integrity:
      • Use "File" > "Info" > "Check for Issues" to detect and repair potential file corruption.
      • Consider using specialized data recovery tools for more severe cases.
    • Analyze Third-Party Add-Ins:
      • Temporarily disable add-ins one by one to see if any are causing conflicts.
      • Update or remove problematic add-ins if confirmed.
    • Verify Macro Security Settings:
      • Check if macros are enabled for "Trusted Documents" or specific locations.
      • Adjust settings cautiously to balance security and functionality.
    • Consult Microsoft Support:
      • If the issue persists, contact Microsoft support for further troubleshooting and potential bug reports.

    4. Prioritization:

    • Prioritize issues based on impact and urgency.
    • Address widespread problems affecting critical workbooks first.

    5. Communication:

    • Keep users informed about the situation and progress made.
    • Share workaround solutions if available while awaiting a permanent fix.

    Additional Tips:

    • Create backups of important workbooks regularly.
    • Consider adopting a version control system for managing changes and rollback if needed.
    • Encourage users to report issues promptly with detailed information (symptoms, steps taken).

    By systematically assessing the causes and taking targeted actions, you can effectively troubleshoot and resolve the macro-enabled workbook issues in your organization. Remember to prioritize critical issues and keep users informed throughout the process.

  • DuriSmaho's avatar
    DuriSmaho
    Copper Contributor
    I am facing the same issues for quite a while now. I maintain approximately 50 macro enabled "tracking files" all of which use user forms, buttons and generally simple text manipulation macros.
    Out of these 50 file, each month I see a random 3-5 files experiencing the same errors randomly without any known logic or cause.
    List of issues: code seemingly disappearing, file crashing on open, file crashing on button press / user form activation
    Only solution that works on my end is using the file open option: "Open and Repair" after doing it and saving the repaired file, it works as nothing happened...
    On top, since feb. I noticed significantly longer macro runtime comparing to previous 3 years of using the same macro... Macro that used to take 3 cells and work with their values in milliseconds now takes up to 10 seconds to do the same, without any modification.

Resources