Password Protect Macro Tutorial

posted in: Excel Tutorials | 0

Password Protect Macro Tutorial

excel tutorials for dummies

Excel Macro Password guide

In this tutorial we will outline how you can Password Protect the macros in Excel using Excel VBA password code. When executed correctly, when a user attempts to run a macro a dialogue box will present which will ask them to insert a password that you have defined. The user can only execute the macro if they enter the correct VBA password. This is useful if you want to prevent unauthorized spreadsheet users from running macros on your workbook, especially if the macro that runs causes irreversible changes that an more novice user may not be aware of.

Before starting with this Excel VBA password guide, you may wish to follow my beginners guide, especially if you are new to Excel Macros.

Excel Macro Password Example

In our working example to help you understand how this works, our macro Inserts a new row and also formats the new row with gridlines, however we have applied a password to our macro so this can only be executed by a user who knows the password.

 

Excel macro password

 

To achieve this you will need to open up the Macro  code. The password protect macro with no code but text outlining where to type your requirements is as follows.

VBA password code

Private Sub CommandButton1_Click()

Dim password As Variant

password = Application.InputBox(“INSERT MESSAGE TO USER HERE“, “INSERT NAME OF INPUT BOX HERE“)

Select Case password

Case Is = False

    ‘do nothing

Case Is = “INSERT YOUR DESIRED PASSWORD HERE

INSERT YOUR MACRO CODE HERE

Case Else

MsgBox “INSERT A MESSAGE TO THE USER IF THEY INPUT AN INCORRECT PASSWORD HERE

End Select

End Sub

In the VBA password code above you can see that I have edited between the parenthesis in BOLD ITALIC CAPITALS to explain what information you need to enter between the parenthesis to tailor this for your own needs.

In my example, our dialogue message to the user is “Please Enter Password” and our input box header is Password Protected Macro.

Our password is “password” and our macro code if the user enters the correct password is

 

 

Sheets(“Sheet1”).Range(“A4”).Select

ActiveCell.EntireRow.Insert shift:=xlDown

Sheets(“Sheet1”).Range(“A4:E4”).Select

Selection.Borders.Weight = xlThin

 

The message to the user if they input an incorrect password is “The password you entered was incorrect”. If we now enter these values into our code using the template above, our code reads as follows.

 

 

Excel Macro Password code

Private Sub CommandButton1_Click()

Dim password As Variant

password = Application.InputBox(“Please Enter Password”, “Password Protected Macro”)

Select Case password

Case Is = False

    ‘do nothing

Case Is = “password”

Sheets(“Sheet1”).Range(“A4”).Select

ActiveCell.EntireRow.Insert shift:=xlDown

Sheets(“Sheet1”).Range(“A4:E4”).Select

Selection.Borders.Weight = xlThin

Case Else

MsgBox “The password you entered was incorrect”

End Select

End Sub

If you follow the same logic then you should easily be able to edit this code for your own purposes and password protect your own excel macros.

An important thing to note is that it is also a good idea to password protect your VBA code from access if you are using this, otherwise people who have familiarity with macros will be able to open your source code and view what the password is or even alter the password. Guide on how to do this will be available shortly.

Password Protect Macro video tutorial

This concludes our Password Protect Macro guide. If this has been helpful then please like or subscribe to the YouTube channel for more tutorials

Leave a Reply