The open xml format for workbooks, unfortunately, also allows worksheet protection to be removed. Of course, you will have to enable macros, if you want to see the hidden sheets 🙂 There is a major limitation you should be aware of! You can download the sample workbook, to see how it works: Download Sample Workbook: Hide Sheets if Macros are not Enabled.xlsm

Sheets('Attention').Visible = xlSheetHidden If Wsh.Name 'Attention' Then Wsh.Visible = xlSheetVisible Hidden sheets will be unhidden only when users click on the “View Worksheets” button, the code is also simple: Private Sub CommandButton1_Click() If Wsh.Name 'Attention' Then Wsh.Visible = xlSheetVeryHiddenĪpplication.Goto Reference:=Sheets('Attention').Cells(1, 'A') Sheets('Attention').Visible = xlSheetVisible Private Sub Workbook_BeforeClose(Cancel As Boolean)

When the workbook is closed, we have to hide the worksheets again, this way the sheets that must remain hidden will be hidden at startup if we do not hide sheets at Workbook_BeforeClose event, hidden sheets will be seen on opening, which is not desired: Option Explicit Using the Workbook_Open event, if the macros are NOT enabled, the User Form where users can choose to view hidden sheets will not be displayed: Private Sub Workbook_Open()Īpplication.Goto Reference:=Sheets('Attention').Cells(1, 'AW') A Macro To Protect Worksheets But Enable Pivot Tables.And here are the VBA codes, located in ThisWorkbook module.Excel Tip-How To Quickly Select All Or Just Parts Of Your Pivot Table.Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook.Final Notes On Macro Settings.įinally, these settings relate only to Excel and not to other Office programs. Disabled by default, it prevents unauthorized programs from changing your macros or building self-replicating harmful codes. Now, this setting controls programmatic access to the object model of VBA. Trust access to the VBA project object model. Any macros and code are allowed to run freely, even those with malicious intent. All unsigned macros are disabled without notice. So, you can choose to enable those signed macros or trust the publisher. If you have not trusted the publisher, a notification is displayed. (only if you have already trusted the publisher). Essentially the same as the setting above, except that if a trusted publisher digitally signs the macro, the macro can run. All macros are disabled but can be enabled on a case by cases basis.ĭisable all macros except digitally signed macros. Only macros stored in a trusted location will run.ĭisable all macros with notification. This option will disable all macros, and none will be able to be run. Macro Settings Explained.ĭisable all macros without notification. Below is an explanation of what each option means.

Disable macros in excel not working