Make Users Enable Macros in Order to View a Workbook in Excel

Tutorial showing you how to make a user enable macros in a workbook in order to view the workbook.

This is very important when macros make-up a critical part of the workbook and not having them can stop the workbook from performing correctly or allow users to mess-up or corrupt the data and functionality of the spreadsheet.

There are a few steps involved in making this work securely and I will go through each of them below.

Steps to Force Users to Enable Macros in Order to View an Excel Workbook

1. Create a worksheet that you want users to see if they have not enabled macros.
   It is a good idea to include a message that tells the user what is going on.  I also removed the gridlines from this sheet just so that the message would be a bit more clear (View tab > then un-check Gridlines).
2. Make sure you have at least one other worksheet tab.  This is where the actual data and anything else you want will go.  You can have as many worksheets as you want but I've used one here to make it easier to follow.
   
3. Hit Alt + F11 on the keyboard to go to the macro editor.
   
4. Double-click where it says ThisWorkbook:
   
5. In the window that opens, copy and paste this code: 

   Private Sub Workbook_Open()

   'worksheets to show when macro is enabled

   Sheets("Data").Visible = True

   'worksheet that shows reminder to enable macro

   Sheets("Reminder").Visible = xlVeryHidden

   End Sub

   Private Sub Workbook_BeforeClose(Cancel As Boolean)

   'worksheet with reminder

   Sheets("Reminder").Visible = True

   'worksheets to show when macro is enabled

   Sheets("Data").Visible = xlVeryHidden

   ThisWorkbook.Save

   End Sub

   In the editor, it will look like this:

    

   This code says that when the user opens the workbook, the Data worksheet will appear and the Reminder worksheet will disappear; but, when the user closes the workbook, the Reminder sheet will appear and the Data sheet will disappear and then the file will be saved, in order to save the hiding of the Data worksheet.

   If the user does not have macros turned-on, they will only ever see the Reminder worksheet and nothing else.

6. Edit the macro to suit your needs.  Change Reminder to the name of the worksheet that will remind users to enable macros.

   Change Data to the name of the worksheet that will contain the data.  For the "Data" worksheet note that this will just be whatever worksheet you want the user to see once they have enabled macros. You can have as many of these as you need; simply copy/paste the line of code for the data worksheet below the other one and change "Data" to the name of the other worksheet.

   It would look something like this:
   

7. Everything should now work, but we still need to secure our macro code so that a user can't break the functionality that we have just created and see the worksheets without having macros enabled - at this point, even just a semi-advanced user, or someone who did a five minute online search, could figure out how to show all of the hidden worksheets without having macros enabled.

   To secure the code, we need to right-click over the vba project, located on the left side of the window, and select VBAProject Properties...
   

8. Go to the Protection tab and check where it says "Lock project for viewing" and enter a password for the project and then hit OK.
   
9. Now close the vba/macro editor window and then close the Excel workbook and save the file when it asks you to do so.  Make sure to save the file as a Macro-Enabled Workbook if you are in Excel 2007 and later or the macro won't work.

   That's it!

As long as the user has macros enabled, they will see the Data tab(s) when the workbook opens.

If they don't have macros enabled, they will only see the Reminder tab when the workbook opens.

Result:

Once macros are enabled:

Editing the Macro in the Future

Don't forget the password you used to lock the project.  When you go back into the VBA editor window and need to edit a macro, you will have to enter the password.

Notes

This is the best way to make a user enable macros in order to use a workbook and it is something that I have used in the past in order to prevent users from messing-up important, and often very complex, workbooks that rely on macros in order to simplify everything.

This technique works even if the user is advanced in Excel because they cannot access the VBA or Macro code in order to edit the desired functionality of the workbook.

However, you should know that this does not encrypt the workbook.  Other programs might still easily be able to open this file and manipulate it if a user really wants the data inside.  Consider this technique as something to be used when you just want to prevent a user from breaking workbook functionality and corrupting data when they use a workbook without having macros enabled.

Sample Workbook

We included a sample Excel workbook with this tutorial so you can see how it works.  The code is also password protected.

The password is: 123456

(Never use that as your real password for Excel or anything else!!!  This is just to make it easier to use this sample workbook.)