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

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)