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.
   
   
   

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



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.
   
   
   
   
3. Drag a Command Button onto your worksheet.
   
   
4. Right-click CommandButton1 and click View code.
   
   
   
   (Hint: be sure Design Mode is selected.)
   
   
5. On the Insert tab 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.
   

   Private Sub CommandButton1_Click ()
   UserForm1.Show
   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:
   
   
   
   Command Button 1
   
   I modified some of the properties for that control
   
   
   
   TextBox 1 
   
   I modified some of the properties for that control
   
   
   
   TextBox 2
   
   I modified some of the properties for that control
   
   
   
   Image 1(Login)
   
   I modified some of the properties for that control
   
   
   
   Image 2(Username)
   
   I modified some of the properties for that control
   
   
   
   Image 3 (Password)
   
   I modified some of the properties for that control
   
   
   
   
2. In the Project Explorer, right-click on UserForm1 and then click View Code.
   
   
3. Add the following code lines.
   

   Public Username As String
   Public Password As String
   Public i As Integer
   Public j As Integer
   Public u As String
   Public p As String
   
   Private Sub CommandButton1_Click ()
       Application.ScreenUpdating = False
       If Trim (TextBox1.Text) = "" And Trim (TextBox2.Text) = "" Then
           MsgBox "Enter username and password.", vbOKOnly
           Else If Trim (TextBox1.Text) = "" Then
           MsgBox "Enter the username ", vbOKOnly
           Else If Trim(TextBox2.Text) = "" Then
           MsgBox "Enter the Password ", vbOKOnly
       Else
           Username = Trim (TextBox1.Text)
           Password = Trim (TextBox2.Text)
           i = 1
           Do While Cells (1, 1).Value <> ""
               j = 1
               u = Cells (i, j).Value
               j = j + 1
               p = Cells (i, j).Value
               If Username = u And Password = p And Cells (i, 3).Value = "fail" Then
                   MsgBox "Your Account temporarily locked", vbCritical
                   Exit Do
                   Else If Username = u And Password = p Then
                   Call clear
                   UserForm1.Hide
                   UserForm2.Label1.Caption = u
                   UserForm2.Label1.ForeColor = &H8000000D
                   UserForm2.Show
                   Exit Do
                   Else If Username <> u And Password = p Then
                   MsgBox "Username not matched", vbCritical + vbOKCancel
                   Exit Do
                   Else If Username = u And Password <> p Then
                   If Cells (i, 3).Value = "fail" Then
                       MsgBox "Your account is blocked", vbCritical + vbOKCancel
                       Exit Do
                       Else If Cells (i, 4).Value < 2 Then
                       MsgBox "Invalid password", vbCritical
                       Cells (i, 4).Value = Cells (i, 4) + 1
                       Exit Do
                   Else
                       Cells (i, 4).Value = Cells (i, 4) + 1
                       Cells (i, 3).Value = "fail"
                       Cells (i, 2).Interior.ColorIndex = 3
                       Exit Do
                   End If
               Else
                   i = i + 1
               End If
           Loop
       End If
       Application.ScreenUpdating = True
   End Sub
   Sub clear ()
       TextBox1.Value = ""
       TextBox2.Value = ""
   End Sub
   Private Sub TextBox1_Enter ()
       With TextBox1
           .Back Color = &H8000000E
           .Fore Color = &H80000001
           .Border Color = &H8000000D
       End With
       TextBox1.Text = ""
   End Sub
   Private Sub TextBox1_AfterUpdate ()
       If TextBox1.Value = "" Then
           TextBox1.BorderColor = RGB (255, 102, 0)
       End If
       i = 1
       Do Until Is Empty (Cells (i, 1).Value)
           If TextBox1.Value = Cells (i, 1).Value Then
               With TextBox1
                   .Border Color = RGB (186, 214, 150)
                   .Back Color = RGB (216, 241, 211)
                   .Fore Color = RGB (81, 99, 51)
               End With
           End If
           i = i + 1
       Loop
   End Sub
   Private Sub TextBox2_Enter ()
       With TextBox2
           .Back Color = &H8000000E
           .Fore Color = &H80000001
           .Border Color = &H8000000D
       End With
       TextBox2.Text = ""
   End Sub
   Private Sub TextBox2_AfterUpdate ()
       i = 1
       Username = TextBox1.Value
       Password = TextBox2.Value
       If TextBox2.Text = "" Then
           TextBox2.BorderColor = RGB (255, 102, 0)
       End If
       Do Until Is Empty (Cells (i, 1).Value)
           j = 1
           u = Cells (i, j).Value
           j = j + 1
           p = Cells (i, j).Value
           If Username = u and Password = p Then
               With TextBox2
                   .Border Color = RGB (186, 214, 150)
                   .Back Color = RGB (216, 241, 211)
                   .Fore Color = RGB (81, 99, 51)
               End With
               Exit Do
               Else If Username = u and Password <> p Then
               TextBox2.BorderColor = RGB (255, 102, 0)
               Exit Do
           Else
               i = i + 1
           End If
       Loop
   End Sub
   Sub settings ()
       With UserForm1
           TextBox1.ForeColor = &H8000000C
           TextBox2.ForeColor = &H8000000C
           TextBox1.BackColor = &H80000004
           TextBox2.BackColor = &H80000004
           TextBox1.Text = "Username"
           TextBox2.Text = "Password"
           TextBox1.BorderColor = RGB (0, 191, 255)
           TextBox2.BorderColor = RGB (0, 191, 255)
           CommandButton1.SetFocus
       End With
   End Sub
   Private Sub UserForm_Initialize ()
       Call settings
   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.
   
   

Demos



The following figure shows the Welcome page after successful login.



The following figure shows entering an invalid password.



The following figure shows login status and login attempt.



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



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

(Hint: Excel source password -> 123987)