Stuck in a situation where you cannot edit a macro on a hidden workbook?
Looking for some quick fixes to easily overcome this cannot edit a macro in a hidden workbook issue?
In that case, you are at the right spot. As today in this post we will help you to know how you can get out of this problematic situation.
Besides this, you will also get to know the reason why you can’t edit macro in personal workbook.
So without wasting any more time, let’s directly dive into this post.
What Does “Cannot Edit Macros On A Hidden Workbook” Error Mean?
Here is the error declaration: “Cannot edit macros on a hidden workbook. Unhide the workbook using the Unhide command.”
Screenshot of this error:
After the occurrence of this error you can’t delete or edit macros on a hidden workbook. So, as to edit them you need to unhide it first.
Why You Cannot Edit A Macro On A Hidden Workbook?
If you are wondering why all of a sudden the edit button in the macros dialog box grayed out. Then read out the following responsible causes behind this:
- Upgraded Excel application to latest version and thus few of its function doesn’t support as per your PC’s configuration.
- Excel workbook is somehow got corrupted.
- Add-ins added to the Excel file is obstructing you from editing its macro.
- Document is saved in xlsx format instead of xlsm.
- Your PC’s security setting is hindering the running of your Excel macros.
How To Fix Cannot Edit A Macro On A Hidden Workbook Error?
Here are best possible fixes to troubleshoot Cannot edit macros on a hidden workbook error are listed down.
3# Unhide The PERSONAL.Xlsb File
4# Save The Document In XLSM Format
5# Modify Trust Center Security Settings
6# Repair Corrupt Excel Workbook
So just go through them one by one…!
1# Unhide The Hidden Workbook
Note: you can’t delete or edit macro from the Excel view in the hidden workbook.
Here are the steps that you need to perform for editing macro in hidden workbook from the Excel view.
- At first go to the Excel Ribbon.
- After that from the menu bar hit the View tab.
- Tap to the Windows Group and then the hit the Unhide option.
- This will open a window in which you will see the entire list of hidden workbooks which are open.
- Now select the workbook which you want to unhide and hit the OK button.
If no password is applied on your Excel workbook, then it will get open unhidden immediately. Or else, you have to feed the password first.
After this, when you try to edit the macro in a hidden workbook then it won’t show any error this time.
2# Disable The Add-Ins
If add-ins are present in your excel file then this can also be the reason behind inability to edit macro in a hidden workbook. So try to edit Excel macros after disabling them once and check whether you are getting the same error message this time.
Here are the steps to disable add-ins in excel:
- Hit the File> Options> Add-ins.
- Now in the Excel option window, select the COM Add-ins from your drop-down menu and hit the Go button.
- Unselect all the checkboxes and hit the OK button.
- Restart your Excel application and check whether the issue has been resolved or not.
3# Unhide The PERSONAL.Xlsb File
If macros which you are willing to delete is present within the PERSONAL.xlsb file then firstly you need to unhide it.
Mostly it is seen that user fails to fetch and unhide this PERSONAL.xlsb file in Excel application. In that case, you can look for this file in the File Explorer.
Usually this file is found in the following location:
C:\Users\UserName\AppData\Roaming\Microsoft\Excel\PERSONAL
- Make a right click on the personal name folder. Now from the menu choose the properties option.
- Just un-tick the hidden property and tap to the OK button.
- Now internet explorers make confirmation whether you want to apply your done changes to the sub files and folders or only to the folder.
- Make selection as per your requirement and then hit the ok.
After this you will see that all the files within that specific folder are no more hidden now. Besides that you can also edit/delete macros present within them.
Helpful article: 5 Ways To Fix Personal Macro Workbook Not Opening Issue
4# Save The Document In XLSM Format
If you are frequently getting this cannot edit a macro on a hidden workbook then the very first thing that you need to figure out is the location where Excel macros are been saved.
If your macros are saved in the document then ensure that you save it in xlsm file format. This is because if you save it in the xlsx file format then macros will get removed of it. But if it is saved in the XLSM file format then you will be able to edit your macros anytime.
In case, macros are not kept in the template instead of document section then you have to load your template directly and then save it back again by using macro-enabled template.
After doing this, you can easily modify your macros within the template.
5# Modify Trust Center Security Settings
Sometime your PC’s security setting hinders you from editing the macros in hidden workbook. So this issue can easily be resolved from the trust center. Here are the steps that you need to follow:
- Hit on the File
- Tap to the Options.
- Now hit on the Trust Center, after then tap to the Trust Center Settings.
- From the Trust Center, tap to the Macro Settings.
- Hit the option ”Enable All Macros”.
- Tap to the OK button.
This time when you open your Excel document, you can easily perform your macros editing.
Note: doing this will allow all the macros to run on your PC. So if you are pretty much sure that you won’t open any malicious code containing documents, then it’s fine. Otherwise rerun the above step and choose the first option i.e disable all macros with notifications.
This time when you try to edit or delete your macro from workbook you will not going to get any error.
6# Repair Corrupt Excel Workbook
If none of the above solution worked in resolving this “cannot edit a macro on a hidden workbook. unhide the workbook using the unhide command”. Then it’s a clear sign that your Excel workbook is somehow got corrupted so you need to repair it immediately. After then only you will be able to regain access to your Excel macros.
Just to cut down your extra time and effort of searching for such a reliable tool I have a well-rounded solution i.e Excel Repair & Recovery tool. This software tool can repair XLS/XLSX files in the most comprehensive manner.
- Very easily repair and recover corrupt Excel files.
- Well compatible for both the Windows and Mac PC.
- Easy options to find and select damaged/corrupted Excel files.
- The software has the capability to fix any issue which is infecting your Excel file.
- Efficiently restore corrupted Excel file by keeping the originality and properties completely intact.
Using the Free trial version of Excel Repair & Recovery tool you can only see the preview of all your recoverable items. After the complete assurance you can buy this tool and save your repaired Excel files.
FAQ (Frequently Asked Question):
Wrap Up:
This is how you can easily fix cannot edit a macro on a hidden workbook error.
I hope you have found this tutorial helpful. If you are facing any other issue related to this specific one then do let us know by sharing your experience on our social media FB and Twitter Page.
If you have something more to share about this specific cannot edit a macro in a hidden workbook issue then don’t hesitate.