Password Protect Macros

Password Protect Macros in Excel

Facebooktwitterlinkedinmail

I confess that today’s tip was motivated by a student asking a question in a recent class. During a discussion about all of the different ways you can password protect a document, the question was asked about how to password protect just a macro.

This technique will allow you to protect your macro code from snoops and ne’er-do-wells, but still allow the document to be opened without a password AND the macro will run without a password. In order to perform these techniques, you’ll need to know how to Turn on the Developer Tab and Record a Simple Macro. By necessity we’ll be dipping into the Visual Basic Editor, but only for a moment.

Using VBE to Password Protect your Macro

  1. Create your macro. For this example I’m going to record a simple macro that enters my first name. The steps to do that are as follows:
    1. Click Record Macro in the Code group on the Developer tab.
    2. Enter the Name example (no spaces allowed).
    3. Enter the Description “This is an example.”
    4. Click OK.
    5. Enter your first name in cell A1.
    6. Click Stop Recording in the Code group on the Developer tab.
  2. Save the document in a macro-enabled format.
  3. Click the Visual Basic button in the Code group on the Developer tab to open the Visual Basic Editor (VBE).
  4. Click the Tools menu.
  5. Choose VBA Project Properties from the dropdown.
  6. Click the Protection tab in the VBA Project Properties window.
    1. Place a checkmark beside Lock Project for Viewing.
    2. Enter the Password twice in the fields.
    3. Click OK.
  7. Close the VBE window or press Alt + Q.
  8. Save your Excel document.
  9. Close and then re-Open the document.
  10. Click your mouse in cell A5.
  11. Click the Macros button in the Code group on the Developer tab.
  12. Click the name of the Macro you just created and then click Run.
  13. Observe that the Macro ran correctly.
  14. Click the Visual Basic button in the Code group on the Developer tab to open the Visual Basic Editor (VBE).
  15. Now you won’t be able to see your code unless you enter the password.
Facebooktwitterlinkedinmail

Leave a Reply

Your email address will not be published. Required fields are marked *

*