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.
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.
⧪ 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.
A dialogue box called Macros will open. Select your desired Macro and click on Edit.
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
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).
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.
- 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.
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.