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

Those who are to 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.


Download Practice Workbook

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


2 Easy Solutions for Cannot Edit a Macro on a Hidden Workbook

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.


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. But if you are interested to know how to create a hidden workbook, you can follow this link.

Conclusion

So, these are the ways to solve the issue to edit a Macro on a hidden workbook. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo