Cannot Edit a Macro on a Hidden Workbook

Macros are great for when you simply want to automate a task. While you can record a macro, you can also manually enter the Visual Basic code to run the macro. Excel also allows you to edit your macro once you’ve created it. However, a number of factors may sometimes make you encounter the “Cannot edit a macro on a hidden workbook” error.

You will mostly encounter this error because your workbook is in the  PERSONAL.xlsb file, and marked Hidden in the properties. Other reasons include the workbook being hidden from the View tab, or if running macros are disabled from the Excel Options.

Unhide Workbook

You may have hidden your workbook to safeguard your worksheets. However, as Excel cannot run the VBA code in such types of workbooks, it will fail to edit your Macro. This is because Excel records your keystrokes and stores them as a Visual basic code.

If you’ve hidden the workbook from the program, you can unhide it from the View tab of your Excel program.

1. Open Excel and hop on to View from the menu bar.
2. From the Window section, select Unhide.
   
3. Select your workbook from the Unhide window > OK.

If the Unhide option is greyed out, it’s because there’s no hidden workbook.

Change Properties for the PERSONAL.xlsb File

The PERSONAL.xlsb files are workbooks that are hidden by default. If your macros are inside this workbook, then you will encounter this error. You will need to change the properties of the PERSONAL.xlsb file from the file explorer.

1. Open File Explorer (Windows key + E).
2. Navigate to C:\Users\<username>\AppData\Roaming\Microsoft\Excel. Change the <username> with your PC’s username.
3. Right-click on the PERSONAL file > Properties.
   
4. On the General tab, uncheck the box next to Hidden.
   
5. Click OK.
6. Select Apply changes to this folder, subholder and files > OK.

Disable Add-Ins

Sometimes, Add-ins in Excel can create problems in the Excel program. This is the case especially true for third-party add-ins. We recommend you only install add-ins from a trusted source to avoid errors as such.

1. Head to File > Options.
2. From the sidebar, select Add-ins.
3. Next to Manage, choose COM Add-ins > Go.
   
4. Uncheck all boxes and select OK.
   

Enable Macros from the Trust Center

You can try enabling macros from the Trust Center in Excel Options. However, before you head on with this method, make sure all of your macros are safe to run. This is because this feature will allow all macros, even the dangerous ones to run.

1. Select File > Options.
   
2. From the Excel Options window, head to Trust Center > Trust Center Settings > Macro Settings.
   
3. Under Macro Settings, select the box next to Enable all macros (not recommended; potentially dangerous code can run).
   
4. Click OK.

Repair Excel using Microsoft Support and Recovery Assistant (SaRA)

If non of the above-mentioned fixes work for you, you can resort to using the Microsoft Support and Recovery Assistant Tool. The SaRA tool is the official Microsoft tool used to troubleshoot issues mostly concerned with Excel and Word. You can install the SaRA tool from Microsoft’s official website and begin troubleshooting.

1. Select Office and Office Apps then click Next.
   
2. Choose the I don’t see my Excel problem listed option.
   
3. Select Next.
4. Sign into your account, if prompted.
5. The tool will then ask you questions based on your problem and give you a solution.