When you add a macro to an Excel workbook, users can see that macro in a list when they choose Tools|Macro|Macros.
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:
If I add the word Private at the start of the Sub MySecretMacro line, that macro won’t be included in the macro list.
Now only the MyVisibleMacro appears in the list.
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.
- 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.
- 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
- Close the Macro dialog box.
- Add Private to the macro code, then use your keyboard shortcut to run it when required.