How to Protect and Hide Excel Macros
Creating a macro in Microsoft Excel allows you to program automatic tasks into a spreadsheet or template that can contain multiple commands or functions for easy access and use. After these macros are present in the worksheet they can be changed by anyone who uses the sheet unless you protect the macros using the password system provided by Microsoft. For protection from having the macros listed, allowing for private use, you can hide the macros completely from user sight.
Open the Excel worksheet where you wish to place the macro and then activate the Visual Basic for Applications options by turning on the Developer tab. To do so, select “Options” from the “File tab, click on “Customize Ribbon,” check the “Developer” box and click “OK” to add the tab to the Excel Ribbon.
Click on the “Developer“ tab on the Ribbon and then select “Visual Basic” to launch the Visual Basic work area.
Open the “Tools” menu and then select the “Visual Basic Properties” option to open the “VBA Project –Project Properties” window.
Click the “Protection” tab and place a check in the box labeled “Lock project for viewing.”
Input a password and confirm the password in the selected sections. Click "OK" to save the password. Now to view the macro content you must enter the password.
Open the VBA macro that you wish to hide from a workbook user's sight when the macro list is chosen.
Locate the first line of the macro beginning with the “Sub” command listing the macro name.
Add the word “Private” at the beginning of the “Sub” line with a space between the word and the name of the macro. Save the macro and it will no longer appear on the list of macros within the workbook.
- View password-protected macros by clicking on the macro in the Project pane of the Visual Basic Editor and then typing the password when prompted.
- View hidden macros by typing the macro name into the Name box to the left of the fx formula bar and pressing “Enter” to open the Visual Basic Editor displaying the hidden Macro’s code.
Larry Simmons is a freelance writer and expert in the fusion of computer technology and business. He has a B.S. in economics, an M.S. in information systems, an M.S. in communications technology, as well as significant work towards an M.B.A. in finance. He's published several hundred articles with Demand Studios.