Hide an Excel Macro

When you add a macro to an Excel workbook, users can see that macro in a list when they choose Tools|Macro|Macros.

MacroListShow

In some workbooks, you might want to hide one or more of your macros, to prevent users from running them.

Note: This technique won’t add any protection to your code, it will simply remove the macro from the list, so casual users won’t accidentally or intentionally run it.

Make the Macro Private

In this example, I have two macros – one that the users can run, and one that I’d prefer to keep hidden. Here’s the code:

MacroListCode

If I add the word Private at the start of the Sub MySecretMacro line, that macro won’t be included in the macro list.

MacroListPrivate

Now only the MyVisibleMacro appears in the list.

MacroListHidden

Run the Hidden Macro

Now that you’ve hidden the macro, so casual users won’t find it, how can you run it yourself? Here are a few ways you can run it

  • Open the Visual Basic Editor (Alt+F11), click somewhere in the macro’s code, then click the Run button.

MacroListRun

OR

  • If you know the name of the macro, type it in the Name Box, at the left of the Formula bar.
  • Press the Enter key, and the Visual Basic Editor will open automatically, with the cursor flashing in the macro’s code
  • Click the Run button.

MacroListName

OR

  • Before you hide the macro, choose Tools|Macro|Macros, select the macro, and click the Options button
  • Type a keyboard shortcut (Ctrl + Shift + M in this example) then click OK

MacroListOption

  • Close the Macro dialog box.
  • Add Private to the macro code, then use your keyboard shortcut to run it when required.

 

______________________

You may also like...

6 Responses

  1. Hi Debrah,

    Two more tips:

    1. Run a hidden macro by hitting alt+F8 and typing its name and cliking run.

    2. You can hide all macros in a module by putting tis at the top:

    Option Private Module.

    See http://www.jkp-ads.com/Articles/DistributeMacro05.asp

  2. JP says:

    You can also hide a macro from the GUI by adding an optional argument to it (which of course you would never actually use).

    For example:

    Sub MySecretMacro(Optional FakeArgument As String)
    ‘ your code
    End Sub

  3. Thanks Jan Karel and JP!

    • Kurt Rosenfeld says:

      Debra,
      Very Clever Lady!
      Thank you.
      I spent hours looking for the right combination and could not figure out why some Procedures werer available to Excel Users and others were not.
      This solved all my problems about calling Public Procedures in a Private Module which are used by a number of Form Codes.
      Kurt

  4. Contextures Blog » Run an Excel Macro From a Cell Reference says:

    […] week I wrote an article about hiding Excel macros, so they don’t appear in the macro list. While working on that article I noticed that the Macro […]

  5. Stefan says:

    If you change the Sub to Private, the keyboard shortcut won’t work any more.

Leave a Reply

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