[Solve:] Cannot Edit a Macro on a Hidden Workbook (2 Easy Solutions)

Get FREE Advanced Excel Exercises with Solutions!

Those who work with Excel VBA very often face this problem sometimes, we try but cannot edit a Macro and it’s shown that you cannot edit a Macro on a hidden workbook. In this article, I’ll show you how you can solve this issue pretty easily and comprehensively.


[Solve:] Cannot Edit a Macro on a Hidden Workbook (2 Easy Solutions)

Here I am trying to edit a Macro from my workbook, but I can’t edit it. A notification box is appearing and it’s telling me that I can’t delete a Macro on a hidden workbook. I am sure those who work with Macros very often have faced this problem at least once in their life.

Cannot Edit a Macro on a Hidden Workbook in Excel

Now you may wonder, why the hell is it shown as a hidden workbook though I’ve opened it and am working on it? Okay, the answer is that the Macro is not actually inside your active workbook, rather it’s inside a different workbook that is hidden (named PERSONAL.xlsb here, check the image), but each time you open any workbook, it’s shown inside it.

Therefore, when you try to edit it, you can’t.

Our objective today is to solve this issue. That is, to edit a Macro on a hidden workbook.

We can solve the issue in two possible ways.


1. Editing a Macro on a Hidden Workbook by Unhiding It First

In this method, we’ll first unhide the hidden workbook, and then delete the Macro on it.

Follow the steps mentioned below to execute this process.

⧪ Step 1: Opening Unhide Dialogue Box from the View Tab

Open the View tab on the Excel ribbon. Then under the section Windows, click on Unhide.

Unhiding Workbook to Solve Cannot Edit a Macro on a Hidden Workbook

⧪ Step 2: Unhiding Workbook from the Dialogue Box

A dialogue box called Unhide will open. Select the name of the hidden workbook (PERSONAL.xlsb here) and click on OK.

⧪ Step 3: Editing the Macro

Now you can edit the Macro. Under the Developer tab, click on Macros from the section code.

Editing the Macro to Solve Cannot Edit a Macro on a Hidden Workbook

A dialogue box called Macros will open. Select your desired Macro and click on Edit.

Editing a Macro to solve cannot edit a Macro on a hidden workbook

You can now edit it.

Read  More: [Fixed!]: Unable to Enable Macros in Excel


2. Editing a Macro on a Hidden Workbook by Using a VBA Code

If you don’t want to follow the above process, you can use a simple VBA code to edit a Macro on a hidden workbook.

⧭ VBA Code:

Sub Edit_a_Macro_on_a_Hidden_Workbook()

Hidden_Workbook_Name = "PERSONAL.XLSB"
Hidden_Macro_Name = "Macro1"
Active_Workbook_Name = "Cannot Edit a Macro on a Hidden Workbook.xlsm"

Windows(Hidden_Workbook_Name).Visible = True
Windows(Active_Workbook_Name).Activate
Application.Goto Reference:=Hidden_Workbook_Name + "!" + Hidden_Macro_Name

End Sub

VBA Code to Solve Cannot Edit a Macro on a Hidden Workbook

⧭ Notes:

Here the name of the hidden workbook is “PERSONAL.XLSB”, the name of the hidden Macro is “Macro1”, and the name of the workbook on which I am working is “Cannot Edit a Macro on a Hidden Workbook.xlsm”. Don’t forget to change those with your ones before running the code (The first 3 lines).

Inputs to the Code to Solve Cannot Edit a Macro on a Hidden Workbook

⧭ Output:

Run the code by pressing the button Run Sub / UserForm from the Visual Basic ribbon above.

The hidden workbook will get unhidden and the editor window will open before you with the Macro. You can now edit it.


Notes

  • Up to this point, we only discussed how we can edit a Macro on a hidden workbook.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

So, these are the ways to solve the issue to edit a macro in a hidden workbook. Do you have any questions? Feel free to ask us.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo