1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Userform Login for Multiple Level User - Customized Spreadsheet

Discussion in 'VBA Macros' started by Dafe, Jul 14, 2015.

  1. Dafe

    Dafe New Member

    Messages:
    8
    Hello Deepak

    I like this tool (User Form Login for Multiple Level User). Can you please tell me the username and password for Admin user and for Creator User. Can you also tell me how to change the login logo to mine and also tell me how to change spreadsheets to my own spreadsheets. I am having 6 to 7 spreadsheets in total that I would like to add to customize the form.

    Please just send me a tutorial file on how to modify the attached file. I am currently using Excel 2007 and I hope the coding is compatible with Excel 2007.

    Thanks!

    Attached Files:

  2. Deepak

    Deepak Excel Ninja

    Messages:
    2,862
    These are userid/password.

    upload_2015-7-14_13-53-57.png

    Login Logo in based in Interface worksheet! You can change it there!

    What u what to manipulate with ur workbook is need to share.

    It is 100% compatible with xl2007.

    All sheets are visible in attached sheet!

    Attached Files:

  3. Deepak

    Deepak Excel Ninja

    Messages:
    2,862
    Check this..

    upload_2015-7-15_11-26-40.png

    Attached Files:

    Thomas Kuriakose likes this.
  4. Dafe

    Dafe New Member

    Messages:
    8
    Hello Deepak

    Thanks for updating the file but why are the spreadsheets displaying before I can enter my username and passcode? Moreover, I would like to give full access for Admin Login and Creator Login to the whole spreadsheets. I only want to grant access for limited spreadsheets to User Login. How do I set permission for User Login to view only selected spreadsheets?

    Please help me to fix this issue and re-send me the file. I would like to make a donation for this project.

    Thanks
  5. Deepak

    Deepak Excel Ninja

    Messages:
    2,862
    Check this..

    Here u can modify the rights..


    upload_2015-7-15_12-19-23.png


    Login

    upload_2015-7-15_12-21-14.png


    You can also send me ur file. either here or in Private messaging to update it.

    Attached Files:

    Thomas Kuriakose likes this.
  6. Dafe

    Dafe New Member

    Messages:
    8
    Hello Deepak

    Can you please help me to adjust the attached file. I am trying to create an email template in excel to send manually to individual customers.

    Can you please help me to modify the code below to include all the information in the body of the outlook email. The outlook template should be similar to the attached image template file. This is my code:

    =HYPERLINK("mailto:"&F2&"?subject="&G2&"&body="&H2,"Send Mail")

    Please download the sample excel file and the outlook email template image and let me know if you can fix this bug or maybe make use of VBA to format the outlook message template.

    Thanks for your time and I await your feedback.

    Regards

    Attached Files:

  7. Deepak

    Deepak Excel Ninja

    Messages:
    2,862
    @ Dafe

    Your new reply doesn't have any co-relation to the subject. So pls start a new thread for the same.
  8. Mehmud Khan

    Mehmud Khan Member

    Messages:
    41
    Hello Deepak and Dafe,

    I just saw your conversation, it's really helpful for my assignment. It's exactly similar like that only which I want.
    I also noticed the @Dafe's question, he had the same query which I was searching.And Deepak easily solves his query.

    It's my humble request from Deepak, could you please look on my query as well, I know it's very simple for you.
    I had attached an excel file, could you please make some changes on the same as per my requirements?

    My requirement is below;


    If someone open the workbook than,he/she can see only Dashboard sheet.
    If someone open the workbook any click on Login button than, he get a popup/form for username and password.

    -Username and Password are stored in "For Admin" sheet of the workbook.
    -If Username and Password are incorrect, than get exit the excel workbook.
    -If Username and Password are correct, than get accessed and proceed for further.

    There are certain conditions, if login get successfully by users, than;
    -If John login correctly than he can see "Dashboard" , "John" and "Help" Sheets only.
    -In "For Admin" sheet, I have restricted the allowed sheets to various users.
    -If users login successfully than, a time-stamp of last login shall be filled automatically in "For Admin" sheet.
    -If user do any modification after login than, Yes/No must be filled automatically in "For Admin" sheet.

    Waiting for a positive reply guys as soon as possible.

    Thanks and warm regards.
    Mehmud

    Attached Files:

  9. Deepak

    Deepak Excel Ninja

    Messages:
    2,862
    Check it.

    Attached Files:

    Thomas Kuriakose likes this.
  10. Mehmud Khan

    Mehmud Khan Member

    Messages:
    41
    Wow....
    Thanks for your instant reply Mr. Ninja!
    Really your valuable reply is appreciable.

    But problem raised while login from "Admin Mode". If I am not able to login from Admin mode than, I cant give authority to various users to open specific sheets.
    Could you please look on this !
  11. Deepak

    Deepak Excel Ninja

    Messages:
    2,862
    Sorry! There was typo error!

    Change it.


    Code (vb):
    For Each rng In Sheets(userDB).Range(SheetRange).Resize(1).Offset(myFind.Row - 2)
        If rng.Value <> "" Then
            If Evaluate("ISREF('" & rng.Value & "'!A1)") Then
                If Sheets(rng.Value).Visible <> -1 Then Sheets(rng.Value).Visible = -1
            End If
        End If
    Next rng

    Set myFind = Nothing
    Application.ScreenUpdating = True
    Unload Me

    If Evaluate("ISREF('" & username & "'!A1)") Then Sheets(username).Activate Else Sheets("Dashboard").Activate
     
    Thomas Kuriakose likes this.
  12. Deepak

    Deepak Excel Ninja

    Messages:
    2,862
    Few more enhancement...

    Attached Files:

    Thomas Kuriakose likes this.
  13. Mehmud Khan

    Mehmud Khan Member

    Messages:
    41
    Really amazing Deepak !!
    And so sorry for the cross-posting, actually I thought no one will give reply on this conversation as this convrstn is very old,that why I created one more query.... Sorry once again...

    And thanks for your initiative....

    I don't want to irritate you once again, but I thing you missed that "Modified (Y/N)" column in "For Admin" sheet, which indicates that user open there worksheet but whether they modified it or not.

    Thanks once again Mr. Ninja Deepak!
    :)
  14. Deepak

    Deepak Excel Ninja

    Messages:
    2,862
    Check it.

    Attached Files:

    Thomas Kuriakose likes this.
  15. Deepak

    Deepak Excel Ninja

    Messages:
    2,862
    Pls check it.

    Attached Files:

    arif120 and Thomas Kuriakose like this.
  16. Mehmud Khan

    Mehmud Khan Member

    Messages:
    41
    Last edited: Sep 11, 2017
    sathikali likes this.
  17. Iinsanesmart

    Iinsanesmart New Member

    Messages:
    9
    Hi Everyone!

    This is a great post, indeed. I have downloaded the document but unfortunately I faced some erros. May I ask you to look into it please, is there any chance to help me with that ? :)

    Well the problem is:
    1. After I login (no matter what username!!), when I am trying to delete one row/column (no matter what worksheet!) I got en error:

    Run-time error '13':
    Type mismatch

    After that I select Debug and the problem is on VBA Code line:

    If bChange <> Target.Value Then



    My question for you is if there is any chance to solve this? I'm working on a company where I need multiple lever user, and this document is exactly what I need (well, I need a license as well - but I will make another thread for this one).


    Thank you,
  18. Deepak

    Deepak Excel Ninja

    Messages:
    2,862
    Iinsanesmart

    Same has been fixed in the attached file. Do let me know for any issue...

    Attached Files:

    arif120 and Iinsanesmart like this.
  19. Iinsanesmart

    Iinsanesmart New Member

    Messages:
    9
    Dear Deepak,

    Thank you so much for your swift and kind reply! I really appreciate it. Unfortunately I have a "gift" on this world, I really enjoy testing files in order to find some errors..

    To be honest with you, the v5 version is better than v4, meaning that I can delete some rows now without getting an error. BUT, please take a look by yourself.

    If you are trying to delete one single row, it's working.

    The problems are:
    * Please login with no matter what username/password and access the document:
    - Please try to delete 2 or more rows - you'll get the same error : Run-time error '13': Type mismatch
    - Please try to COPY & PASTE 2 or more rows - you'll get the same error: Run-time error '13': Type mismatch


    Once again I want to thank you for your patience,

    My best regards,
    Narcis
  20. Deepak

    Deepak Excel Ninja

    Messages:
    2,862
    Iinsanesmart

    Sorry as it was mY silly mistake & love to solve the same.

    Pls change the below & check.

    Code (vb):

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Not myrow > 1 Then MsgBox "Pls re-login.", vbCritical: login: Exit Sub

    If Target.Cells.Count > 1 Then
        GoTo xChange
    Else
        If Not Application.Intersect(Target, Cells()) Is Nothing Then
            If bChange <> Target.Value Then GoTo xChange
        End If
    End If
    Exit Sub

    xChange:
        Application.EnableEvents = False
            Sheets(userDB).Cells(myrow, 4).Value = "YES"
        Application.EnableEvents = True

    End Sub
    arif120 and Iinsanesmart like this.
  21. Iinsanesmart

    Iinsanesmart New Member

    Messages:
    9
    Hello Deepak,

    Thank you so much for your kindness, it's really impressive :) and really appreciate it.


    Ahaha, you just made my day, I swear! There is absolutely no problem mate, you're perfectly fine!

    Regarding the new code, it's perfect with one little problem!!! AAHAHAHA, you really don't want to see my workbook at this moment, it's a mess!

    I tried to find another /bug/ and to be honest with you, I found one!!!! I'm really sorry :))) .


    Please go into one cell and write something, then go to another one and write something as well - you'll see that it's okay. Now please select (by mistake!!) 2 or more cells with your mouse and write something, you'll receive the same error!

    Like I've before mentioned that you don't want to see my workbook at this moment because I've tried to find another problem and I did.. I apologize for it!


    Yours sincerely,
    Narcis
  22. Deepak

    Deepak Excel Ninja

    Messages:
    2,862
    Pls check...

    Code (vb):

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Not myrow > 1 Then MsgBox "Pls re-login.", vbCritical: login: Exit Sub

    If Not IsArray(bChange) Then
        If Target.Cells.Count = 1 Then
            If Not Application.Intersect(Target, Cells()) Is Nothing Then
                If bChange = Target.Value Then Exit Sub
            End If
        End If
    End If

    With Sheets(userDB).Cells(myrow, 4)
        If .Value <> "YES" Then
            Application.EnableEvents = False
                .Value = "YES"
            Application.EnableEvents = True
        End If
    End With


    End Sub
    arif120 and Iinsanesmart like this.
  23. Iinsanesmart

    Iinsanesmart New Member

    Messages:
    9
    Dear Deepak,

    I did. Everything good now but with one exception as well. I know that I'm bothering you a lot, but what should I do ?:))

    I've inserted a table, I did what I did and when I tried to CUT/PASTE the table from one sheet to another:

    Run-time error '1004':
    Method 'Intersect' of object '_Application' failed


    Code (vb):
     If Not Application.Intersect(Target, Cells()) Is Nothing Then
    Yours sincerely,
    Narcis
  24. Deepak

    Deepak Excel Ninja

    Messages:
    2,862
    I didn't encounter any issue on table too.
    arif120 and Iinsanesmart like this.
  25. Deepak

    Deepak Excel Ninja

    Messages:
    2,862
    If still issue then check with it.

    Attached Files:

    arif120 and Iinsanesmart like this.

Share This Page