Excel VBA Login Form
VBA Login is a unique way to protect excel workbook and worksheets. Although we have options of Protect Sheet and protect workbook which is available in Review menu option, which we have the fix patterns of locking the worksheet and workbook. With the help of the VBA login, we have created a customize login box by which we can create dialog box or login form in which we can authorize the login id and password to access the complete excel workbook and any specific worksheets. For better understanding, suppose we have an Excel file that has multiple sheets and each sheet has some confidential data to be protected. And each data is meant for everyone, so we can create a login form by giving every user their login id and password and user will only be able to open and access the data which is meant for him. This could be done only by VBA Login.
How to Generate Login User Form in VBA?
We will learn how to generate logic user form in Excel by using the VBA Code.
Example #1
In this example, we will learn how to create login box using User form. For this, follow the below steps:
Step 1: For this open VBA and from the Insert menu tab select UserForm.
Step 2: Now once we get the UserForm opened, from the toolbox select the Label option and create a label box in Userform.
Step 3: At the left hand side we have a Properties of the Label which we have created. We can change the name, font, size, and color of created label.
Step 4: As you can see in below screenshot, we have change the name user form as Label1, Font as Arial Narrow, Fore Color as Blue and Caption as Enter User Name. We can further customize it.
Step 5: Now we will create a text box here to enter the user name for login as shown below.
Step 6: Now we would insert a label for entering the password.
Step 7: Again we would change the name of Label, color, font, size, and name as required below.
Step 8: Insert a text box for entering the password against the Password label.
Step 9: And last, create a login button with the help of the Command button to click on it.
Step 10: Now edit the properties of created Command Button from the Properties window at left.
Step 11: We are done with part of creating a login form in VBA. Now we will write a small VBA code to make this login work. For this, double click anywhere on created UserForm and in that write the sub-procedure as shown below.
Code:
Private Sub Login_Click() End Sub
Step 12: Open an IF – End IF loop and write the condition as if the Login flag is FALSE then activate the worksheet and then show login.
Code:
Private Sub Login_Click() If LoginFlag = False Then Worksheets("Sheet1").Activate LogIn.Show End If End Sub
Step 13: Now we will compile the code by pressing the F8 key and And run the code by pressing the Play button located below the menu bar, we would get the Login box.
Example #2
Although there are various ways to create a VBA Login. But we will be seeing another code where we will be using IF – End IF loop to list the login. For this, again follow the same process to create a UserForm which we have seen in example-1. Include 2 labels and 2 text boxes, each for entering User Name and Password.
Step 1: We can use the same User Form which we have seen in example-1 as there is no change in the pattern of login box or create a new one. Now double click on User form and write the sub procedure. Now in the open the IF – End IF loop for the condition, if Username box is blank then give me the message as “Enter Login Credentials”, else message “Enter Username”.
Code:
Private Sub Login_Click() If UsernameBox.Value = "" Then If PasswordBox.Value = "" Then MsgBox "Enter Login Credentials!" Else MsgBox "Enter Username" End If End Sub
Step 2: Now define the Else If condition again, if username value is USER1 and password is Blank then give me the message as “Enter Password” and if the password is “abcd” then hide the application or else give the message as “Re-Enter Login Credentials.”
Code:
Private Sub Login_Click() If UsernameBox.Value = "" Then If PasswordBox.Value = "" Then MsgBox "Enter Login Credentials!" Else MsgBox "Enter Username" End If ElseIf UsernameBox.Value = "USER1" Then If PasswordBox.Value = "" Then MsgBox "Enter Password" ElseIf PasswordBox.Value = "abcd" Then Me.Hide: Application.Visible = True Else MsgBox "Please Re-Enter Login Credentials." End If End Sub
Step 3: At last list the final IF condition for if the login password is incorrect or blank, then return the message “Enter Correct Credentials”.
Code:
Private Sub Login_Click() If UsernameBox.Value = "" Then If PasswordBox.Value = "" Then MsgBox "Enter Login Credentials!" Else MsgBox "Enter Username" End If ElseIf UsernameBox.Value = "USER1" Then If PasswordBox.Value = "" Then MsgBox "Enter Password" ElseIf PasswordBox.Value = "abcd" Then Me.Hide: Application.Visible = True Else MsgBox "Please Re-Enter Login Credentials." End If Else If PasswordBox.Value = "" Then MsgBox "Enter Password" Else MsgBox "Please Enter Correct Credentials." End If End If End Sub
Step 4: Open a new sub procedure for Command Button which we created for Login, and write the below line for quitting the application.
Code:
Private Sub CommandButton2_Click() ThisWorkbook.Application.Quit End Sub
Step 5: And at last, write the final sub procedure for Created UserForm name to close the application.
Code:
Private Sub LoginArena_QueryClose(Cancel As Integer, CloseMode As Integer) ThisWorkbook.Application.Quit End Sub
Step 6: Final would look like this below.
Step 7: Now compile the each and every step of the code and run it then. In the popped up login form, Enter the user name as USER1 and the password as “abcd” in designated section as per code. If we press ENTER or click on LOGIN, then the code will be exited and we will be returned to the VBA window.
Pros & Cons of VBA Login
- VBA Login allows different users to login in a different way in a single Excel worksheet.
- We restrict the user to the data which is meant for him/ her.
- With the help of VBA Login, we can customize the excel workbook and give the different login user id and password to different users to access the data.
- Because of lengthy lines of code, the process of creating VBA Login is quite complicated.
Things to Remember
- We can use Protect Worksheet and Protect Workbook which also consider the password for login and accessing the data. But the password would be the same for each login. Whereas VBA Login allows use to create multiple user id and password.
- Always create a simple user form first, because when we customize the data the size and attributes of created shapes and boxes, we may end of complicating the coding part.
- We can create VBA login to direct the user to his login area after login.
- Once coding is done, save the Excel in macro-enabled excel format.
Recommended Articles
This is a guide to the VBA Login. Here we discuss how to Generate Login User Form in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –
