Results 1 to 3 of 3

Thread: Need help to create a login user form in Microsoft Excel

  1. #1
    Join Date
    Feb 2012

    Need help to create a login user form in Microsoft Excel

    On a user form being used as a LogOn, I have two textboxes...txt1 & txt2. and Commandbutton1 = Login Commandbutton2 = Quit. The end user will use txt1 as a UserName entrance & txt2 as a PassWord entrance. The enduser will use Commandbutton1 to Login once username and password is correct. they will use Commandbutton2 to Quit the Workbook and it should not give the user the option to Save before exiting at this point. What code do i use to make this work. So when the user types in the correct username and password - this data is store in sheet4 - (column A = Username, column B = Password) it will open the workbook. If username or password is incorrect a message will appear saying "Please check the username or password is correct". Also the cross ( X ) button on the TOP RIGHT HAND CORNER of the userform should be disabled - so it stops the user from clicking the X to enter. Maximum users is 5. Can someone help with code and instruction where I need to input the codes.

  2. #2
    Join Date
    Aug 2011

    Re: Need help to create a login user form in Microsoft Excel

    If you are new to VBA programing you may want to pick up a book or to on how to program in Excel using VBA. I don't really think this forum is really for teaching users from Step 1. You need to become a little familiar with user forms and their event procedures and in some cases especially the sequence of the operation. In any case, I have listed some code for you use. I want to caution you on two things. Even with this code in place, a somewhat savy user will be able to gain access to the workbook without being one of the authorized users. Also, please take caution when placing this code in you workbook. I strongly suggest that you make a sample or test workbook to play around with and keep the original workbook untouched until you are thoroughly satisfied that the code works the way you want it t. Then you can export the code modules and user modules from the test book and then import them into the original workbook.

    Create a new workbook with a minimum of 4 worksheets. One of them MUST be named "Sheet4". In Sheet 4, leave cell A1 & B1 blank for now. We'll use your ID later here. Starting in cell A2 type in the names of the other users ( A2, A3, A4 & A5 ) along with their passwords in B2, B3, B4 & B5. Switch to code view (Alt. F11). Place the following code in a standard module:
    Option Explicit
    Sub Auto_Open()
    End Sub
    Sub Auto_Close()
    With ActiveWorkbook
    .Sheets("Sheet4").Visible = False
    .Protect "XXX", True, True
    End With
    End Sub
    Public Sub CloseWorkbook()
    Call Auto_Close
    End Sub
    Create a new userform and add the following controls:
    • Add (2) labels and change their captions to "UserID" and "Password".
    • Add (2) text boxes. Change their names to "txt1" and "txt2".
    • Add (2) command buttons. Change their captions to "Login" & "Cancel".

    Option Explicit
    Dim Users(4, 1) As String
    Dim LoginValid As Boolean
    Private Sub CommandButton1_Click()
    Dim X As Integer
    Dim iCounter As Integer
    If iCounter > 2 Then GoTo SubExit
    For X = 0 To UBound(Users, 1)
    If txt1.Text = Users(X, 0) And txt2.Text = Users(X, 1) Then
    LoginValid = True
    GoTo SubExit
    End If
    Next X
    iCounter = iCounter + 1
    If MsgBox("Try Again?", vbYesNo + vbCritical, "Login Failed") = vbYes Then
    GoTo Start
    End If
    Unload Me
    End Sub
    Private Sub UserForm_Initialize()
    Dim X As Integer
    ' Read UserNames & ID's
    ' Save in Array
    For X = 0 To 4
    Users(X, 0) = Sheets("Sheet4").Cells(X + 1, 1)
    Users(X, 1) = Sheets("Sheet4").Cells(X + 1, 2)
    Next X
    LoginValid = False
    End Sub
    Private Sub UserForm_Terminate()
    If LoginValid = False Then
    Call CloseWorkbook
    ActiveWorkbook.Unprotect "XXX"
    End If
    End Sub
    Save the workbook, close the workbook and re-open it. Leave both the User ID and the Password blank, click OK and you should see the workbook. Note that in the code, I set the password to protect the workbook as XXX, and also unprotect the workbook in code. The Auto_Open subroutine runs automatically, each time the workbook is opened, starting the login dialog. The auto_clos subroutine automatically sets the password to XXX, saves to workbook and then closes the workbook. If the login fails, the workbook will close. After you done playing with the code and understand how it works, you can start experimenting with some alterations to customize it for you specific needs. Also, don't forget to add your Username & Password after you have played around with it for a while.

  3. #3
    Join Date
    Aug 2011

    Re: Need help to create a login user form in Microsoft Excel

    The code below is ideal for what i want and need, but I have added it to my excel, I can do as much as it open the workbook when no username and password is entered, but when I use the other un and pw's in Sheet4, i get the Try Again, and when I click yes it doesn't let me retry. Please help, this is perfect for my new workbook. How do you customize to add more users, I have 15 users in total. Each time I customize, get an error.

Similar Threads

  1. Create a user control form in Excel 2010
    By --MasseySian-- in forum Software Development
    Replies: 3
    Last Post: 06-09-2012, 07:03 PM
  2. Replies: 4
    Last Post: 11-01-2012, 03:56 PM
  3. Unable to create a user profile on terminal server upon login.
    By Alex Galkin in forum Windows Server Help
    Replies: 1
    Last Post: 21-05-2011, 12:57 AM
  4. Login Failed For User - Microsoft SQL Server, Error: 4064
    By Udayachal in forum Windows Software
    Replies: 4
    Last Post: 05-04-2010, 04:45 PM
  5. How to Create login form for a weber
    By Reckon in forum Software Development
    Replies: 3
    Last Post: 10-08-2009, 12:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts