Ad Clicks :Ad Views : Ad Clicks :Ad Views : Ad Clicks :Ad Views : Ad Clicks :Ad Views :
    Excel formulas and tutorials

    Excel formulas and useful tools

    21 Comments

      • First of all, thank you very much for your kind support and appreciated efforts which is being provided over here. Excellent job.

        Secondly, I’ve faced a problem after login through the form on my sheet, found that functions are not automatically working, needs to be re-updated manually.

        Example

        If my formula was

        C1=sum (A1+B1),

        Results will not appear automatically, until it will be updated manually every time I make a change. Hope you have an advice for me.

        anyway, thank you for you great effort once again.

         
        • The file is set to Manual Calculation. Set it to Automatic from excel menu: Formulas/Calculation Options

           
    1. Dear Ravindra,

      You’re welcome!
      In the cmdOK_Click() procedure you can insert a query from a database or, if you want to keep it static, you can add more users and passwords in this procedure:
      if strconv(trim(……..)=”GUEST” or strconv(trim(…..)=”GUEST2″ then
      ………………..
      if strconv(trim(……)=”PA$$” or strconv(trim(……..)=”PA$$2″ then

      This is it!
      Kind regards!

       
      • do you have macros enabled? does the login form appear?
        use credentials guest and pa$$

        to view the VBA code use PA$$

         
    2. Hello sir, it is very usefull. I want to change user id, how can we do that. I also couldn’t understand where to paste multiple user login codes. Please help

       
    3. Okk. I found it. But it also allow “Guest1” with the Password of “Guest2” and vice versa. Any solution?

       
      • Hi Rajat,

        Sorry for the late reply.
        I amended and replaced the xlsm file, so now a list of users and passwords could be accommodated inside sheet Usr. Please open the VBA editor (PA$$) and set the visible attribute to Visible. When you save the file, this sheet will get VeryHidden so that it will not be available for anyone.
        Regards!

         
      • Hi Ramlee,

        Be sure you have set the visibility property to “visible” for the Usr sheet in the VBA editor. There are 3 user defined.
        Regards!

         
    4. Dear Sir

      Thank you so much for making this available (new to VBA), much appreciated. I need to restrict some sheets in the workbook, eg HR department can only see their HR sheet. Would it be possible for you to give me guidance how to add this to your template?

      Kind Regards

      Martie Snyders

       
      • Hi Martie,

        You have to add department code to each user in the usr sheet (which is very hidden) and use this code in the login module:

        Private Sub cmdOK_Click()
        Dim j As Integer

        j = 2
        found = 0
        While Sheets(“Usr”).Cells(j, 1) <> “” And found = 0
        department = “”
        If Sheets(“Usr”).Cells(j, 2) = Trim(txtName.Text) And Sheets(“Usr”).Cells(j, 3) = txtPassword.Text Then
        found = 1
        department = Sheets(“Usr”).Cells(j, 4)
        Unload Me
        End If
        j = j + 1
        Wend
        If department = “HR” Then
        Sheets(“HR”).Visible = xlSheetVisible
        Else
        Sheets(“HR”).Visible = xlSheetVeryHidden
        End If

        If found = 0 Then
        MsgBox “Wrong user name or password!”, vbCritical, “Error”
        txtName.SetFocus
        End If

        End Sub

         
    5. Thanks for providing this useful tool.
      If security setting is macro disable, then this login form will not work. How to solve this problem?

       
      • Hi Steve,
        Excel was not built to allow high security on worksheets. Because it is very easy to build viruses in VBA, Microsoft disables macros by default. You cannot change this setting by VBA code so you need to find ways to convince the user to activate macros.
        All you can do is to hide all the sheets inside the file (very hidden property), except a welcome sheet, so that a user doesn’t see any data if he doesn’t enable macros and logs in with the right credentials. Set a password to VBA so that users cannot access VBA and cannot change the very hidden property of sheets. You must know that there are simple ways to break any such password. There are excel files on the internet which change VBA and sheet protection passwords using just VBA code.
        Best regards!

        The conclusion is that this login form code is working just for people that are not interested in breaking rules.

         
    6. HI
      IT’S VERY GOOD BUT I NEW LEARNER FOR VBA AND I CAN NOT OPEN the VBA editor (PA$$).SO PLEASE HELP ME

       
      • Hi Prakash,

        I tested again the excel file on this article and VBA password (PA$$) works fine. How else can I help you?

         
      • Hi Shambhu,

        As an example: Sheets(“Usr”).Cells(j, 2) refers to the value located on row j from the 2nd column in the Usr sheet. This sheet is VeryHidden.
        Best reagrds!

         

    Leave a Comment

    Your email address will not be published. Required fields are marked *

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    This div height required for enabling the sticky sidebar