Lỗi can not edit a macro on hiden workbook năm 2024

Macros are great for when you simply want to automate a task. While you can record a macro, you can also manually enter the Visual Basic code to run the macro. Excel also allows you to edit your macro once you’ve created it. However, a number of factors may sometimes make you encounter the “Cannot edit a macro on a hidden workbook” error.

You will mostly encounter this error because your workbook is in the PERSONAL.xlsb file, and marked Hidden in the properties. Other reasons include the workbook being hidden from the View tab, or if running macros are disabled from the Excel Options.

Unhide Workbook

You may have hidden your workbook to safeguard your worksheets. However, as Excel cannot run the VBA code in such types of workbooks, it will fail to edit your Macro. This is because Excel records your keystrokes and stores them as a Visual basic code.

If you’ve hidden the workbook from the program, you can unhide it from the View tab of your Excel program.

1. Open Excel and hop on to View from the menu bar.
2. From the Window section, select Unhide.
3. Select your workbook from the Unhide window > OK.

If the Unhide option is greyed out, it’s because there’s no hidden workbook.

Change Properties for the PERSONAL.xlsb File

The PERSONAL.xlsb files are workbooks that are hidden by default. If your macros are inside this workbook, then you will encounter this error. You will need to change the properties of the PERSONAL.xlsb file from the file explorer.

1. Open File Explorer (Windows key + E).
2. Navigate to` C:\Users\\AppData\Roaming\Microsoft\Excel`. Change the with your PC’s username.
3. Right-click on the PERSONAL file > Properties.
4. On the General tab, uncheck the box next to Hidden.
5. Click OK.
6. Select Apply changes to this folder, subholder and files > OK.

Disable Add-Ins

Sometimes, Add-ins in Excel can create problems in the Excel program. This is the case especially true for third-party add-ins. We recommend you only install add-ins from a trusted source to avoid errors as such.

1. Head to File > Options.
2. From the sidebar, select Add-ins.
3. Next to Manage, choose COM Add-ins > Go.
4. Uncheck all boxes and select OK.

Enable Macros from the Trust Center

You can try enabling macros from the Trust Center in Excel Options. However, before you head on with this method, make sure all of your macros are safe to run. This is because this feature will allow all macros, even the dangerous ones to run.

1. Select File > Options.
2. From the Excel Options window, head to Trust Center > Trust Center Settings > Macro Settings.
3. Under Macro Settings, select the box next to Enable all macros (not recommended; potentially dangerous code can run).
4. Click OK.

Repair Excel using Microsoft Support and Recovery Assistant (SaRA)

If non of the above-mentioned fixes work for you, you can resort to using the Microsoft Support and Recovery Assistant Tool. The SaRA tool is the official Microsoft tool used to troubleshoot issues mostly concerned with Excel and Word. You can install the SaRA tool from Microsoft’s official website and begin troubleshooting.

1. Select Office and Office Apps then click Next.
2. Choose the I don’t see my Excel problem listed option.
3. Select Next.
4. Sign into your account, if prompted.
5. The tool will then ask you questions based on your problem and give you a solution.

Note: You need an Office 365 subscription to perform this Advanced search.

Asmi is a content writer specializing in MS Office. She loves writing Excel-related content to assist other users to learn their way around the program. She started using Excel when she first entered the industry as a marketing intern. Soon, she realized that learning the often-overlooked spreadsheet program was no piece of cake. In her articles, she attempts to brief out complex topics to help beginner to intermediate-level Excel users. Asmi uses Excel for everything, including creating a grocery list to managing her personal finance. She’s a bit dramatic when it comes to the Flash Fill feature; so much so that could write a eulogy out of it. When she’s not working, you’ll find her learning a new recipe, or cleaning up her mess (out of the failed recipe).