MACRO SECURITY – Password Protect a VBA Project

Creating a Macro in Microsoft Excel helps you automate tasks in a spreadsheet. Once these codes are present in the worksheet they can be changed intentionally or accidently by anyone who uses the sheet unless you password protect them. Learn how to password protect a VBA Project. This feature allows you to prevent a user from viewing or even executing any VBA code that you create. This is a great feature for adding security to your Macros.

(1) Prevent a user from VIEWING a VBA Project

Password Protect a VBA Project

Let us first create a simple macro that you want to protect.

(1) Insert a command button on your worksheet and add the following code

(2) Right Click the CommandButton1 and click ‘View Code’ and then copy this code: Range(“D10”).Value = “We need to password protect this macro” 

(3) Once you have added the code, click Tools > VBAProject Properties…

(4) On the Protection tab, check “Lock project for viewing” and enter a password twice and click OK.

(6) Now save the Workbook and close it. Just remember that it needs to be saved as an “Excel Macro-Enabled Workbook (*.xlsm)”. Then reopen the Excel file and try to view the code.The following dialog box will appear:

You can still execute the code by clicking on the command button but you cannot view or edit the code now (unless ofcourse you know the password). The password for the downloadable Excel file is “password”.

(2) Prevent a user from EXECUTING a VBA Project

If you want to password protect the macro from being executed, add the following code lines:

CODE

Dim password As Variant

password = Application.InputBox("Enter password to proceed", "Password Protected")


Select Case password


  Case Is = False
'do nothing
Case Is = "itsasecret"
Range("D10").Value = "We need to password protect this macro"
Case Else
MsgBox "You entered an incorrect password. Try again."


End Select

It will look like this:

You’ll get the following prompt when you click the command button on the sheet to run the macro:

How does it work?

The macro uses the InputBox method of the Application object. If the user clicks Cancel, this method returns False and the InputBox disappears (nothing happens). Only when the user enters the password correctly (“itsasecret”), the code will be executed. If the entered password is incorrect, a MsgBox is displayed.

P.S: The user can’t view the password in the VB Editor because the project is protected from being viewed.

Hope you found this useful !!