Lock Macro Execution (Using Password)

In the last post, we learnt how to lock a VBA project with a password. So if a user wants to see the code then he/she has to input the correct password. But we realized, although the codes are locked, users can run the macros from the developer tab. But there may be times you want to also lock the macros from being executed as well. Then users will need to provide the password before running the macro. So in this post you can learn how to lock the macro execution using a password.

Let’s consider this simple macro

Sub WriteNumbers()

     Dim i As Integer

     For i = 1 To 10000
          Worksheets("Sheet1").Range("A" & i).Value = i
     Next i

End Sub

What this macro does is it writes the numbers from 1 to 10000 in column A of the Sheet1. Now let’s look at how we can modify the above macro to protect it from being executed. First we need to declare an additional variable.

Dim Res As String
Dim i As Integer

Then we can use an InputBox as follows.

Res = InputBox("Please enter the password!", "Password")

Now the program will ask for the password when the user tries to run the macro.

Once the user enters the password, the program needs to verify whether it is correct or not. We can use an IF Statement for this. Also we can use the StrComp function to compare the user input with the password. Assume our password is “atyo45#4Yt”. Then we can develop the validating section as follows.

If StrComp(Res, "atyo45#4Yt", vbTextCompare) = 0 Then
     For i = 1 To 10000
          Worksheets("Sheet1").Range("A" & i).Value = i
     Next i
Else
     MsgBox "Sorry! Incorrect password"
     Exit Sub
End If

StrComp function compare the user input and the password (atyo45#4Yt). Then if the user input and password are matching then “If StrComp(Res, "atyo45#4Yt", vbTextCompare) = 0” becomes true. So the program goes to the For Next loop and writes the numbers. If the user input and password are not matching then the program shows the below message.

This is the completed code.

Sub WriteNumbers()

     Dim Res As String
     Dim i As Integer

     Res = InputBox("Please enter the password!", "Password")

     If StrComp(Res, "atyo45#4Yt", vbTextCompare) = 0 Then
          For i = 1 To 10000
               Worksheets("Sheet1").Range("A" & i).Value = i
          Next i
     Else
          MsgBox "Sorry! Incorrect password"
          Exit Sub
     End If

End Sub

However there is one small problem in this macro. Typically, if a user clicks the cancel button of the input box or closes the input box, the program should not do anything. It should stop the execution without even showing any message. But in this macro,if a user cancels the Input box or closes it, still the program shows the password incorrect message. This is because when the user cancels or closes the input box it returns an empty string. So to avoid this what we can do is we can first check whether the variable “Res” holds an empty string. If it holds an empty string program will not perform any other actions. Here is the improved macro.

Sub WriteNumbers()

     Dim Res As String
     Dim i As Integer

     Res = InputBox("Please enter the password!", "Password")

     If Res = "" Then
          'Do nothing
     ElseIf StrComp(Res, "atyo45#4Yt", vbTextCompare) = 0 Then
          For i = 1 To 10000
               Worksheets("Sheet1").Range("A" & i).Value = i
          Next i
     Else
          MsgBox "Sorry! Incorrect password"
          Exit Sub
     End If

End Sub

However note that, when a user clicks the “OK” button without entering password (i.e. When the user input an empty string as password) the program doesn’t show the password incorrect message. Instead the program behaves similar to when a user clicks the cancel button.