[Fixed] Cannot Edit Macro on a Hidden Workbook

Macros in Excel help you customize and automate tasks easily. Sometimes, you cannot edit a macro because the workbook is hidden or protected. It prevents you from making necessary changes or updates.

Generally, the problem occurs because the workbook is protected, saved in the wrong format, or security settings block access. Sometimes, conflicting add-ins can also block macros from editing.

In this article, we will walk you through the most common reasons why you can’t edit a macro on a hidden workbook and provide you with step-by-step instructions to fix each problem.

If a workbook containing macros is hidden, you won’t be able to edit its code. You may have hidden your workbook to safeguard it. You need to unhide the workbook from the View tab before accessing the macros.

Steps:

➤ Open your Excel file.
➤ Go to View >> Unhide from the Window section.

➤ Select the hidden workbook you want to unhide from the Unhide window >> Click OK.

The PERSONAL.xlsb file is where workbooks are hidden by default. If the PERSONAL.xlsb file is hidden at the system level, Excel won’t show it for editing.

Steps:

➤ Press  Win  +  E  to open File Explorer.
➤ Navigate to this path-C:\Users\<username>\AppData\Roaming\Microsoft\Excel
➤ Here, change the username to the username of your PC.
➤ Right-click PERSONAL.xlsb File >> Properties >> General >> uncheck the box next to Hidden.

➤ Click Apply >> OK.

Macros only work when the file is saved as a Macro-Enabled Format. If your workbook is saved as .xlsx, macros won’t run or be editable. You need to save the file in the .xlsm (macro-enabled) format to fix the problem.

Steps:

➤ Open your Excel file.
➤ Go to File >> Save As.
➤ In the Save as type dropdown, choose Excel Macro Enabled Workbook >> Save.

Some Excel add-ins can interfere with macros and make it unable to edit. So, when you are unable to edit a macro, disabling the problematic add-ins can fix the issue.

Steps:

➤ Go File >> Options >> Add-ins.
➤ Set Manage to COM Add-ins >> click Go.

➤ Uncheck all the listed Add-ins >> Click OK.

In some cases, Excel may block macros for security purposes. That’s especially true if it is from unknown sources. So, if the macros are disabled, you won’t be able to edit them. You will need to enable the macros through Trust Center settings.

Steps:

➤ Go to File>> Options >> Trust Center >> Trust Center Settings.

➤ Select Macro Settings >> Select Enable all macros (not recommended; potentially dangerous code can run)

➤ Click OK.

If the macro of your workbook is locked, you won’t be able to edit it. You will need to access the Project Properties and enter the correct password for editing if you have permission.

Steps:

➤ Go to the Developer tab >> Visual Basic

➤ Select Tools >> VBAProject Properties

➤ Click the Protection tab in the properties window
➤ If “Lock project for viewing” is checked, the project is protected.
➤ Uncheck “Lock project for viewing” >> Enter the correct password >> Click OK

Frequently Asked Question

Do macros work on hidden sheets?

Yes, macros can run on hidden sheets. However, they may require specific adjustments to the code or visibility settings. For instance, if the macro relies on selecting a sheet (using the Select method), it will fail on a hidden sheet. That’s because you can’t select a hidden sheet directly.

How do I show hidden macros in Excel?

➤ Press  Alt  +  F11  to open the VBA Editor.
➤ Expand each workbook in the Project Explorer to see available macros.
➤ If the macro is inside a hidden workbook like PERSONAL.xlsb, unhide it first by going to View > Unhide

How do I remove hidden macros from Excel?

➤ Press  Alt  +  F11  to open the VBA Editor
➤ Find your workbook and expand its modules in Project Explorer.
➤ Delete the macros inside the modules.

Wrapping Up

If you can’t edit a macro because the workbook is hidden, it’s usually due to the visibility of the workbook, file properties, or security settings. You can fix most of the problems quickly by following the steps above. Feel free to download the sample file and share your thoughts and suggestions.