Login module in excel vba

 Today in this tutorial we would learn how to create a login module in Excel so that we can control the multiple users.

First of all we need to think how to develop this module step by step.

Step 1. create and design the user forms

Step 2. transfer the data from userform  to worksheet

Now go to developer tab then visual basic and insert a userform  and designed it as follow

First of all we need to design the userform for those user who are new here. So that we need to insert 5 textbox and 5 level from toolbox.

TextBoxbox1 is for name who is access

Textbox2 is for user ID

Text box3 is for enter password

Textbox4 is for confirm password

Text box5 is for secret code which  is necessary for if user forget their password then we will verify the user and how to know the user who forget password. so we put such a code that user need to fill user ID that is textbox2 first and click on the button that is forget password botton. it is remember that we need to insert a command button in login user from as named forget password.

Now insert a command botton in the the userform  which is for new user that is my current userform as name as submit it can be renamed from properties if you wish.

Now double click on submit button and write the below code where we need to set the worksheet where data will transfer from userform. So we create a variable and set it as follow

Dim sh as worksheet

where it needed to insert a sheet as named Privacy Page it is very very confidential for your workbook.

Now create a variable for find out the last row where we transfer the data from userform. So,

Dim i as long

i = sh.Range("A" & Rows.Count).End(xlUp).Row + 1  

 Where sh.Range("A" & Rows.Count).End(xlUp).Row is last non empty row

‘Sheet3 - 1

Option Explicit

Private Sub CommandButton1_Click()

UserForm1.Show

                          End Sub

...........               ....................................................

‘ThisWorkbook - 1

Option Explicit

Private Sub Workbook_Open()

Application.Visible = False

frmLogin.Show

                            End Sub

‘frm_new_user - 1

Private Sub CommandButton1_Click()

If Me.TextBox3.Value <> Me.TextBox4.Value Then

MsgBox "Password does not match", vbOKOnly, "Re-Type the Password"

Exit Sub

End If

Dim m As Integer

For m = 1 To 5

If Me("TextBox" & m) = "" Then

MsgBox "All Fields are mandatory"

Exit Sub

End If

Next m

'data transfer from textbox to worksheet

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Privacy Page")

Dim i As Long

i = sh.Range("A" & Rows.Count).End(xlUp).Row + 1

sh.Cells(i, 1) = Me.TextBox2.Value

sh.Cells(i, 2) = Me.TextBox3.Value

sh.Cells(i, 3) = Me.TextBox1.Value

sh.Cells(i, 4) = Me.TextBox5.Value

sh.Cells(i, 5) = Me.TextBox2.Value & Me.TextBox3.Value

Unload Me

MsgBox "Congratulation !"

End Sub

.............................................

Option Explicit

Private Sub cmdClear_Click()

    Me.txtUserID.Value = ""

    Me.txtPassword.Value = ""

    Me.txtUserID.SetFocus

End Sub

.....................................................

Private Sub cmdLogin_Click()

'to count the number of click on this button

Static x As Integer

x = x + 1

If x >= 3 Then

MsgBox "You have tried maximum number"

Unload Me

ThisWorkbook.Close

Exit Sub

End If

' if id or password does not match

If Me.TextBox1.Value <> Me.txtUserID.Value & Me.txtPassword.Value Or Me.txtUserID = "" Then

MsgBox "Wrong user id or password"

Exit Sub

End If

'if id and password both matched

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Privacy Page")

    Dim user As String   

    Dim password As String 

    user = Me.txtUserID.Value

    password = Me.txtPassword.Value

 Dim i As Long

For i = 2 To sh.Range("A1000").End(xlUp).Row

    If sh.Cells(i, 5) = user & password Then

         Unload Me

        Application.Visible = True     

        ' To remember who is Active user for this session

        sh.Range("H1").Value = user

 'to control user access insert a module and call it here

         Call hide_sheet     

End If

Next i

'to set up the page after login

Sheets("sheet3").Activate

 End Sub

...................................................

Private Sub CommandButton1_Click()

' for forget password button

' condition1

If Me.txtUserID = "" Then

MsgBox "Type Correct user id first"

Exit Sub

End If

'if condition1 satisfied check the secrete code by inputbox

If InputBox("Type secrete code") = Me.TextBox2.Value Then

Me.TextBox3.Visible = True

Me.Label1.Visible = True

End If

End Sub

...............................................

' insert a command button in worksheet

Private Sub CommandButton2_Click()

' for new user

frm_new_user.Show

End Sub

.........................................................................

Private Sub txtUserID_AfterUpdate()

'data show from worksheet to textboxes

Me.TextBox1 = ""

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Privacy Page")

Dim i As Long

For i = 2 To sh.Range("A1000").End(xlUp).Row

'condition

If sh.Cells(i, 1) = Me.txtUserID Then

Me.TextBox1.Value = sh.Cells(i, 1) & sh.Cells(i, 2)

Me.TextBox2.Value = sh.Cells(i, 4)

Me.TextBox3.Value = sh.Cells(i, 2)

End If

Next i

End Sub

...........................................................................

Private Sub UserForm_Initialize()

    Me.txtUserID.Value = ""

    Me.txtPassword.Value = ""  

    Me.txtUserID.SetFocus

    'hide textboes that are very confidential

Me.TextBox3.Visible = False

Me.Label1.Visible = False

End Sub

.......................................................

Now insert module and type as per below code

Option Explicit

Sub Enable_Disable_button()

Dim sh As Worksheet

Set sh = Sheets("Privacy Page")

' If user is not Admin then you can control what you want to hide or unhide

If sh.Range("H1").Value <> "Admin" Then

With UserForm1

   .CommandButton1.Enabled = False

   .CommandButton2.Visible = False

   End With

   End If 

End Sub

................................................

Sub hide_sheet()

Dim sh As Worksheet

Set sh = Sheets("Privacy Page")

 ' When user is not Admin then Strickly hide the privacy page or another page as you wish

If sh.Range("H1").Value <> "Admin" Then

sh.Visible = xlSheetVeryHidden  'means no one can unhide this page

ThisWorkbook.Sheets("Sheet2").Visible = xlSheetVeryHidden

Else

sh.Visible = xlSheetHidden 'means admin can unhide this page

ThisWorkbook.Sheets("Sheet2").Visible = xlSheetHidden

End If

End Sub

......................................................

Double click on ThisWorkbook and select Open and put the below code. so that When You Open the workbook, it will hide your workbook(Application) and pop up login userform

Option Explicit

Private Sub Workbook_Open()

Application.Visible = False

frmLogin.Show

End Sub

......................................................................................................

Double click on login userform and select QueryClose and put the below code. so that When You close the login userform then it will close your workbook also

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

If CloseMode = 0 Then ThisWorkbook.Close

End Sub

Click here to download this file