Create Login Application In Excel Macro Using Visual Basic

This article is mainly focused on creating applications using the Visual Basic for Applications (VBA) programming language. With Excel VBA you can automate tasks in Excel using what is called a macro. Create a simple macro that will be executed after clicking on a command button.

Chapter 1

In this chapter, learn how to turn on the Developer tab. The Developer tab is not visible by default in Excel, we must configure it to show that tab. This tab contains a variety of tools that allows development and customization of Excel macro applications.

This guidance applies to Excel 2010.

  1. Start the Excel application.

  2. On the File tab, click the Options button.

    The following figure shows the File tab and Options button in Excel 2010.

     File tab and Options button in Excel

  3. Choose the Customize Ribbon button in the Excel Options dialog box.

  4. Check the Developer check box.

    The following figure shows the Customize Ribbon in Excel 2010.

    Customize the Ribbon

  5. Click OK to save the selection changes.

Chapter 2

This chapter teaches you how to create a simple login form in Excel VBA. The User form we will create looks as follows.

The following figure shows the simple login form in Excel VBA.

simple login form in Excel VBA

Step 1

To place a command button on your worksheet and assign a macro:

  1. On the Developer tab click Insert.

  2. In the ActiveX Controls group click the Command Button.

    ActiveX Controls group

  3. Drag a Command Button onto your worksheet.

  4. Right-click CommandButton1 and click View code.

    view code

    (Hint: be sure Design Mode is selected.)

  5. On the Insert tab click New User Form.

    click new User Form

  6. Right-click Sheet1 and click View code to write the code between Private Sub CommandButton1_Click () and End Sub.

  7. Add the code line shown below.
    1. Private Sub CommandButton1_Click ()  
    2. UserForm1.Show  
    3. End Sub  

Step 2

Add Controls.

  1. Add the controls listed in the table below. Clicking on control from the Toolbox next you can drag a control on a User Form:

    User Form

    Command Button 1

    I modified some of the properties for that control

    Command Button 1

    TextBox 1 

    I modified some of the properties for that control

    Textbox 1

    TextBox 2

    I modified some of the properties for that control

    Textbox 2

    Image 1(Login)

    I modified some of the properties for that control

    Login

    Image 2(Username)

    I modified some of the properties for that control

    Username

    Image 3 (Password)

    I modified some of the properties for that control

    Password

  2. In the Project Explorer, right-click on UserForm1 and then click View Code.

  3. Add the following code lines.
    1. Public Username As String  
    2. Public Password As String  
    3. Public i As Integer  
    4. Public j As Integer  
    5. Public u As String  
    6. Public p As String   
    7.   
    8. Private Sub CommandButton1_Click ()  
    9.     Application.ScreenUpdating = False  
    10.     If Trim (TextBox1.Text) = "" And Trim (TextBox2.Text) = "" Then  
    11.         MsgBox "Enter username and password.", vbOKOnly  
    12.         Else If Trim (TextBox1.Text) = "" Then  
    13.         MsgBox "Enter the username ", vbOKOnly  
    14.         Else If Trim(TextBox2.Text) = "" Then  
    15.         MsgBox "Enter the Password ", vbOKOnly  
    16.     Else  
    17.         Username = Trim (TextBox1.Text)  
    18.         Password = Trim (TextBox2.Text)  
    19.         i = 1  
    20.         Do While Cells (1, 1).Value <> ""  
    21.             j = 1  
    22.             u = Cells (i, j).Value  
    23.             j = j + 1  
    24.             p = Cells (i, j).Value  
    25.             If Username = u And Password = p And Cells (i, 3).Value = "fail" Then  
    26.                 MsgBox "Your Account temporarily locked", vbCritical  
    27.                 Exit Do  
    28.                 Else If Username = u And Password = p Then  
    29.                 Call clear  
    30.                 UserForm1.Hide  
    31.                 UserForm2.Label1.Caption = u  
    32.                 UserForm2.Label1.ForeColor = &H8000000D  
    33.                 UserForm2.Show  
    34.                 Exit Do  
    35.                 Else If Username <> u And Password = p Then  
    36.                 MsgBox "Username not matched", vbCritical + vbOKCancel  
    37.                 Exit Do  
    38.                 Else If Username = u And Password <> p Then  
    39.                 If Cells (i, 3).Value = "fail" Then  
    40.                     MsgBox "Your account is blocked", vbCritical + vbOKCancel  
    41.                     Exit Do  
    42.                     Else If Cells (i, 4).Value < 2 Then  
    43.                     MsgBox "Invalid password", vbCritical  
    44.                     Cells (i, 4).Value = Cells (i, 4) + 1  
    45.                     Exit Do  
    46.                 Else  
    47.                     Cells (i, 4).Value = Cells (i, 4) + 1  
    48.                     Cells (i, 3).Value = "fail"  
    49.                     Cells (i, 2).Interior.ColorIndex = 3  
    50.                     Exit Do  
    51.                 End If  
    52.             Else  
    53.                 i = i + 1  
    54.             End If  
    55.         Loop  
    56.     End If  
    57.     Application.ScreenUpdating = True  
    58. End Sub  
    59. Sub clear ()  
    60.     TextBox1.Value = ""  
    61.     TextBox2.Value = ""  
    62. End Sub  
    63. Private Sub TextBox1_Enter ()  
    64.     With TextBox1  
    65.         .Back Color = &H8000000E  
    66.         .Fore Color = &H80000001  
    67.         .Border Color = &H8000000D  
    68.     End With   
    69.     TextBox1.Text = ""  
    70. End Sub  
    71. Private Sub TextBox1_AfterUpdate ()  
    72.     If TextBox1.Value = "" Then  
    73.         TextBox1.BorderColor = RGB (255, 102, 0)  
    74.     End If  
    75.     i = 1  
    76.     Do Until Is Empty (Cells (i, 1).Value)  
    77.         If TextBox1.Value = Cells (i, 1).Value Then  
    78.             With TextBox1  
    79.                 .Border Color = RGB (186, 214, 150)  
    80.                 .Back Color = RGB (216, 241, 211)  
    81.                 .Fore Color = RGB (81, 99, 51)  
    82.             End With  
    83.         End If  
    84.         i = i + 1  
    85.     Loop  
    86. End Sub  
    87. Private Sub TextBox2_Enter ()  
    88.     With TextBox2  
    89.         .Back Color = &H8000000E  
    90.         .Fore Color = &H80000001  
    91.         .Border Color = &H8000000D  
    92.     End With  
    93.     TextBox2.Text = ""  
    94. End Sub  
    95. Private Sub TextBox2_AfterUpdate ()  
    96.     i = 1  
    97.     Username = TextBox1.Value  
    98.     Password = TextBox2.Value  
    99.     If TextBox2.Text = "" Then  
    100.         TextBox2.BorderColor = RGB (255, 102, 0)  
    101.     End If  
    102.     Do Until Is Empty (Cells (i, 1).Value)  
    103.         j = 1  
    104.         u = Cells (i, j).Value  
    105.         j = j + 1  
    106.         p = Cells (i, j).Value  
    107.         If Username = u and Password = p Then  
    108.             With TextBox2  
    109.                 .Border Color = RGB (186, 214, 150)  
    110.                 .Back Color = RGB (216, 241, 211)  
    111.                 .Fore Color = RGB (81, 99, 51)  
    112.             End With  
    113.             Exit Do  
    114.             Else If Username = u and Password <> p Then  
    115.             TextBox2.BorderColor = RGB (255, 102, 0)  
    116.             Exit Do  
    117.         Else  
    118.             i = i + 1  
    119.         End If  
    120.     Loop  
    121. End Sub  
    122. Sub settings ()  
    123.     With UserForm1  
    124.         TextBox1.ForeColor = &H8000000C  
    125.         TextBox2.ForeColor = &H8000000C  
    126.         TextBox1.BackColor = &H80000004  
    127.         TextBox2.BackColor = &H80000004  
    128.         TextBox1.Text = "Username"  
    129.         TextBox2.Text = "Password"  
    130.         TextBox1.BorderColor = RGB (0, 191, 255)  
    131.         TextBox2.BorderColor = RGB (0, 191, 255)  
    132.         CommandButton1.SetFocus  
    133.     End With  
    134. End Sub  
    135. Private Sub UserForm_Initialize ()  
    136.     Call settings  
    137. End Sub  

Step 3

Test the user form using the following.

  1. Exit the Visual Basic Editor, enter the labels and data shown below into rows.

  2. Deselect the Design mode selection then click Command Button 1 on the sheet.

    Command Button 1 on the sheet

Demos

Demos

The following figure shows the Welcome page after successful login.

welcome page

The following figure shows entering an invalid password.

enter invalid password

The following figure shows login status and login attempt.

login status and login attempt

The following figure shows an account blocked after trying the wrong password 3 times.

account block after trying 3 time

Thanks for reading. I hope this article useful for VBA beginners.

(Hint: Excel source password -> 123987)

Up Next
    Ebook Download
    View all
    Learn
    View all