If you’ve ever opened a workbook and noticed in your ribbon that you can’t edit macros in the same way as usual when editing any other module, well, don’t worry! Your saved workbook needs to be updated in a few steps.
First, click the “File” tab and select “Options.” Second, open up the “Excel Options” tab by clicking on it. A list of options will appear. You can see the “Enable this option” checkbox for each option. Notice in the screenshot below, there is an option called “Security Options,” so click on it to open it up.
Now you need to configure Excel to allow macros to be edited as normal when hidden. Select the “Security” tab and enable the “Always allow macros to be edited in workbooks” checkbox. That’s it! Excel will now allow macros in hidden workbooks to be edited just like in workbooks that are not hidden.
How do you know a workbook is hidden?
You can tell if a workbook is hidden by looking in the title bar of the workbook. If it is hidden, the name of the workbook will be in parentheses, and if you hover over it, it will indicate that it is hidden. If you want to unhide a workbook that has been previously hidden, simply right-click on any cell in the spreadsheet and select “Unhide.” You can unhide multiple workbooks at once by selecting all of them at once with ctrl+click.
The most frustrating workbook situation is unable to edit a macro on a hidden workbook. In this post, we will explain how to fix it with some easy steps.
A series of instructions that are coded to do an action, like open something or copy text is macro. Sometimes when people have a hidden workbook with a macro in it, they can’t edit or run the macro because the workbook is hidden. Here’s what you need to do:
- Right-click on the command button you want to edit and select “customize shortcut”.
- Click “personalize” in a new window.
- Click “user-defined”.
- Select the macro you want to edit, then select “properties” at the top of the list. You may have to scroll back up and find where it is.
- Select “address bar”, then select the options you want to change (uncheck “show in macro editor” if it isn’t there).
- Select “running macros” and then select the command button you want to run your macro from.
That’s it! But, if you get a message in the bottom right corner saying that your macro is hidden, then we have further steps to do.
- Right-click on the command button and select “properties”.
- Tap on the “macros” tab.
- Select the macro you want to use.
- Uncheck “hidden” and make sure “run this code when the workbook opens” is checked.
- Click ok and then close the Customize window. Now run your macro or save it and close it, if you are done editing the macro.
Edit Macro In Personal Workbook
If you still can’t edit a custom macro in a personal workbook, then we will have to use a different method:
- Right-click on the command button you want to edit and select “customize shortcut”.
- Select “Personalize”. Many people have had issues with this step. They close out of the customize window without saving their changes or having the changes go through. When it comes back the macro is gone, or the customizations are not there any longer.
- Check the “hidden” box. If you don’t do this, then the macro might not be able to run at all.
- Click on “options”.
- Scroll down to “running macros” and select that you want to run your macro from that command button. Now close out of the customizing window and it’ll save the changes.
Now you will have to close and re-open your workbook. When you do, your macro should run as it normally would.
Read More: Excel Sum Not Working
Personal Macro Workbook In The Startup Folder
As an alternative, this method could be used if you don’t want to edit macros on a hidden workbook.
- Right-click on the command button you want to edit and select “customize shortcut”.
- Select “Personalize”. Many people have had issues here as well. They close out of the customize window without saving their changes or having the changes go through. When it comes back the macro is gone, or the customizations are not there any longer.
- Check the “hidden” box. If you don’t do this, then the macro might not be able to run at all.
- Click on “options”.
- Select “files and folders” and select the folder you want to use for your personal workbook. Close out of customizing window now and it will save your changes.
Now you will have to close and re-open your workbook. When you do, your macro should run as it usually would.
Final Word
This is a very troublesome situation for people when they can’t edit a macro on a hidden workbook. Hopefully, these instructions will help you next time you are in this situation. If you’ve any queries, please leave us a comment below.