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

  • 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

Dafe

New Member
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!
 

Attachments

Deepak

Excel Ninja
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!
 

Attachments

Dafe

New Member
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
 

Dafe

New Member
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
 

Attachments

Deepak

Excel Ninja
@ Dafe

Your new reply doesn't have any co-relation to the subject. So pls start a new thread for the same.
 
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
 

Attachments

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 !
 

Deepak

Excel Ninja
Sorry! There was typo error!

Change it.


Code:
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
 
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!
:)
 

Iinsanesmart

New Member
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,
 

Iinsanesmart

New Member
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
 

Deepak

Excel Ninja
Iinsanesmart

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

Pls change the below & check.

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

Iinsanesmart

New Member
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
 

Deepak

Excel Ninja
Pls check...

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

Iinsanesmart

New Member
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:
 If Not Application.Intersect(Target, Cells()) Is Nothing Then
Yours sincerely,
Narcis
 
Top