Skip to main content

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.

InputBox

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.

Password Incorrect 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.

Comments

Post a Comment

Popular posts from this blog

How to Add a Formula to a Cell Using VBA

In this lesson you can learn how to add a formula to a cell using vba. There are several ways to insert formulas to cells automatically. We can use properties like Formula, Value and FormulaR1C1 of the Range object. This post explains five different ways to add formulas to cells. Table of contents How to add formula to cell using VBA Add formula to cell and fill down using VBA Add sum formula to cell using VBA How to add If formula to cell using VBA Add formula to cell with quotes using VBA Add Vlookup formula to cell using VBA We use formulas to calculate various things in Excel. Sometimes you may need to enter the same formula to hundreds or thousands of rows or columns only changing the row numbers or columns. For an example let’s consider this sample Excel sheet. In this Excel sheet I have added a very simple formula to the D2 cell. =B2+C2 So what if we want to add similar formulas for all the rows in column D. So the D3 cell will have the formula
Post a Comment
Read more

Download a file from url using VBA

Sometimes our Excel VBA applications need to interact with websites. Downloading a file through a URL is a typical example. In this lesson you can learn how to do that using XMLHttpRequest and ADODB.Stream object. XMLHttp is used to request the data from the web server. Once we receive the data from the server, the ADODB.Stream object is used to write that data to a file. You can use this method to download file types such as image files, csv files etc. So let’s start writing our macro to download a file using the Uniform Resource Locator (URL). Let’s name our Sub procedure as “DownloadFileFromURL” Sub DownloadFileFromURL() End Sub First we need to declare a few variables. Dim FileUrl As String Dim objXmlHttpReq As Object Dim objStream As Object Next assign the URL of the file to the FileUrl variables FileUrl = "URL of your file" For an example if your file URL is https://www.example.com/images/chart.jpg then you can assig
Read more

Fill a Listbox From an Array

In this lesson you will learn how to fill a Listbox from an array. Listboxes are a very useful control type available in the VBA toolbox. You can use the listboxes for search and view functions of the VBA programs. First let’s fill the listbox using a multidimensional array. After that we can try it with a one dimensional array. This is the sample Excel sheet I’m going to use for this lesson. And here is the listbox we are going to fill with the array. So we have data in three columns and fourteen rows. First, we are going to add this data to an array. After that we can add the array to our listbox in the VBA form. You might wonder why we need to add the data to an array. Because data can be directly added to the listbox from the worksheet using VBA. Yes it is possible. But here our objective is to learn how to add data to a listbox from an array. Because there are situations where we need to add the data which is not available in worksheets to listboxes using VBA. For
Post a Comment
Read more