EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

VBA Login

By Ashwani JaiswalAshwani Jaiswal

Home » VBA » Blog » VBA Input & Output Functions » VBA Login

VBA Login

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

You can download this VBA Login Excel Template here – VBA Login Excel Template

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.

VBA Login Example 1

Step 2: Now once we get the UserForm opened, from the toolbox select the Label option and create a label box in Userform.

VBA Login Example 1-2

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.

Popular Course in this category
All in One Excel VBA Bundle (120+ Courses)120+ Online Courses | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (2,356 ratings)
Course Price

View Course

Related Courses

VBA Login Example 1-3

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.

VBA Login Example 1-4

Step 5: Now we will create a text box here to enter the user name for login as shown below.

VBA Login Example 1-5

Step 6: Now we would insert a label for entering the password.

VBA Login Example 1-6

Step 7: Again we would change the name of Label, color, font, size, and name as required below.

Properties window Example 1-7

Step 8: Insert a text box for entering the password against the Password label.

Text Box Example 1-8

Step 9: And last, create a login button with the help of the Command button to click on it.

Command button Example 1-9

Step 10: Now edit the properties of created Command Button from the Properties window at left.

Properties window Example 1-10

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

VBA Login Example 1-11

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

IF – End IF loop Example 1-12

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.

VBA Login Example 1-13

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

VBA Login Example 2-1

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

Else If condition Example 2-2

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

VBA Login Example 2-3

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

CommandButton2 Example 2-4

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

Quit Application Example 2-5

Step 6: Final would look like this below.

VBA Login Example 2-6

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.

VBA Login Example 2-7

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 –

  1. VBA Name Worksheet
  2. VBA Input
  3. VBA Solver
  4. VBA Selecting Range

All in One Excel VBA Bundle (120+ Courses)

120+ Online Courses

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
VBA Input & Output Functions
  • VBA Input & Output
    • VBA Login
    • VBA Outlook
    • VBA Print
    • VBA UserForm
    • VBA Close UserForm
    • VBA Send Email From Excel
    • VBA Debug Print
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

© 2020 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA Login

Forgot Password?

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you
Book Your One Instructor : One Learner Free Class

Let’s Get Started

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

Download VBA Login Excel Template

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

Special Offer - All in One Excel VBA Bundle (120+ Courses) Learn More