Macro has been an essential part of Excel since its early days. It automates repetitive tasks by recording and executing specific sets of actions. However, a user can face various errors while using it. The “Cannot edit a macro on a hidden workbook” is one such error we will discuss in this article.
Employing macros in Excel is easy and effective. Some people use it by recording actions while others prefer using codes via VBA to automate tasks through macros. But most errors occur from receiving Excel files with macros from others. Locating the reason behind those errors is a troublesome task.
Our goal for this article is to give you all the necessary information to solve the cannot edit a macro on a hidden workbook error. We will discuss four effective solutions for this problem. This article will be an easy-to-follow visual guide for you to solve the problem once and for all.
Let’s get started!
Contents
Cannot Edit a Macro On a Hidden Workbook: 4 Easy Solutions
Getting errors while editing or deleting Macros on hidden Excel workbooks is a painful experience. Most of the time, the reason behind the error is unknown. And you cannot apply just any solution to this problem.
The biggest concern comes from the fear of losing data from the file if you follow unconventional methods to solve this problem. However, we aim to present a series of solutions here that will always work without messing up your data.
When the “Cannon edit a macro on a hidden workbook” problem occurs, you get an error window like the one in the image below.
In this section of the article, we will discuss 4 ways you can solve the problem effortlessly. You may follow all the solutions one after another if you are not sure of the reason behind the error message.
Let’s start with the first solution!
Solution 1: Saving the File in XLSX or XLSM Format
In usual scenarios, Excel files with Macros are saved in XLS (Excel 97-2003 Workbook) or XLSM (Excel Macro-Enabled Workbook) formats. On the other hand, the XLSX (Excel Workbook) format is the default Excel file format and is Macro-free. Yet, when an Excel file with Macros on a hidden workbook is saved, the format may become deceptive.
Typically, you can see the file format and determine if it has macros. But it becomes impossible to figure out when a hidden workbook with macros is present in the file. As a result, saving the file in XLSX or XLSM format may eliminate the Cannot Edit a Macro On a Hidden Workbook error.
The process is simple. Go to File in your Excel program when the file is opened. Then click on the Save as option. In the Save As window, click on the Save as type drop-down list and then select either Excel Workbook (.xlsx) or Excel Macro-Enabled Workbook (.xlsm) format from it. Finally, click the Save button.
Now open the newly saved file and see if the problem still persists. In the case that it does, follow the next solution.
Solution 2: Changing Add-In Settings
Excel has a wide range of support for add-ins, from integrated ones to powerful third-party options. Add-ins in Excel make life easier for any user. However, besides making work seamless, add-ins may also create recurring problems.
When you get errors like the “Cannot Edit a Macro On a Hidden Workbook”, disabling enabled add-ins can get rid of the problem. You will need to disable both Excel Add-ins and COM Add-ins for this process. Follow the steps below.
1. First, disable Excel Add-ins. Go to File in Excel and then click Options. The Excel Options window should pop open. On the left, find the Add-ins tab and click on it. Then at the bottom of the window, you will find the Manage option with a drop-down list. Ensure the Excel Add-ins option is selected, then click the Go button.
2. A little Add-ins option window will open. Remove all the checkmarks from the add-ins and then click OK.
You will be taken back to the previous Add-ins tab in the Excel Options window.
3. Now click on the drop-down list next to Manage and select COM Add-ins from the menu. Afterward, click the Go button.
4. Now remove checks from the add-ins in this COM Add-ins window. Finally, click the OK button.
You will see the Excel Options window again. Click OK here as well.
Now you should be able to edit or delete Macros in your workbook. If it still does not work, give the following solution a try.
Solution 3: Unhiding the Workbook
The error dialogue box for the “Cannot Edit a Macro On a Hidden Workbook” problem suggests this solution. Given the error message and reasoning behind it, it might seem to be the most obvious solution. However, we are introducing this solution in the third because, in most cases, it needs to be tried in a sequence after following the previous two solutions.
Let’s follow the simple process for this operation. Open the file that is giving you the error message. Then go to the View ribbon. Next, find the Unhide button inside the Window group and click on it.
Afterward, go to the Macros option, and see if your problem has been resolved. By now, the error should disappear, and you should be able to edit or delete a Macro from your file.
But if you still cannot do it, the problem might be in your Excel settings rather than your file. For that, follow the next solution closely.
Solution 4: Changing the Trust Center Settings
The three solutions we have discussed so far consider the “Cannot edit a Macro on a hidden workbook” error to be originating in the Excel file. Especially the first and third solutions focused on the file itself rather than Excel’s internal settings. But the approach changes in this solution.
When an Excel file is shared with others, and they open it on different devices, Excel’s Trust Center puts a new layer of security on it. The VBA and Macros get disabled by default due to the possibility of having dangerous codes and macros operations. Trust Center often blocks Macros and may initiate the Cannot Edit a Macro error.
Changing one particular Macro setting within the Trust Center has often solved the problem. We bring it to you, too, as one of the most effective solutions.
Go to File in Excel. Then click Options to open the Excel Options window. In it, find the Trust Center tab on the left and click on it. Afterward, on the right, click the Trust Center Settings button. It should open the Trust Center window.
Next, click the Macro Settings tab in the Trust Center window. Then on the right, find the Macro Settings section. Then, click to select the Enable VBA Macros (not recommended; potentially dangerous code can run) option. Finally, click OK.
You will return to the Excel Options window. Click OK to finish the process. Now Macros in your workbook should be editable. You will not get the error message anymore.
Final Words
Macros in Excel are highly useful for increased productivity. During everyday operations, it can stop working for many reasons. The “Cannot edit a macro on a hidden workbook” is one of the most occurring errors while editing or deleting a macro in your workbook.
We talked about four working solutions for this specific problem. Solutions one and three in this article eliminate the problem from your files. Solutions two and four get into the Excel settings to tweak some options and get rid of the error.
If you are entirely unsure what might be causing you to see the “Cannot edit a macro on a hidden workbook” error, you should follow all the methods we have discussed in this article. We strongly recommend maintaining the sequence while you follow the solutions for consistency.
And after applying all the techniques one after another, you will stop getting the error anymore. You will be able to edit or delete macros from your workbook again without any problem at all!