Dear Experts
Please find attached file
Last sheet is for user database
I need to give rights as per username
eg.
if username is Manisha then she can view only "capital purchase" and "Advertising" sheets only
If user name is Sandeep then he can edit all sheets
Please help
Atul
Last edited by atulexel79; 12-04-2014 at 11:52 AM. Reason: Spelling wrong
Hi there,
Open the attached workbook using the UserName "Sandeep" - this will give you access to the "User Database" worksheet where you will see the various UserNames and the various worksheet names.
Insert an "x" in the cell at the appropriate UserName / Worksheet Name intersection to make that worksheet available to that User.
If you insert an "x" in the "All" column, all worksheets will be made available to the selected User - it is then not necessary to insert an "x" in each of the individual worksheet columns.
Hope this helps - please let me know how you get on.
Regards,
Greg M
You might try this worksheet.
Currently There are three users
Manisha (password Manishapw)
Sandeep (password Sandeeppw)
admin (password admin)
usernames are not case sensitive. passwords are case sensitive.
Manisha and Sandeep can change their passwords when they log in.
Admin can add users and change access for each user.
Note that access can be like Manisha's that ALLOWS access to only the Master Sheet, Capital Purchase and Advertizing.
or like Sandeeps that DENYs access to none.
If a new sheet is added Sandeep can access it.
(This is a work in progress and I'd like any comments, particularly about how intuitive the admin function is.
Note: If a username begins with "admin" that is an administrator e.g. "admin Mary", "admin Smith")
Last edited by mikerickson; 12-04-2014 at 11:50 PM.
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
Hi again,
Follow-up to previous post
Please note that (as per the example workbook you posted) the code determines the name of the User from the "Application.UserName" property, and not from the name entered on the login form.
The code can be altered to use the name entered on the login form if this is what you would prefer.
Regards,
Greg M
Last edited by Greg M; 12-05-2014 at 06:14 AM. Reason: Workbook added - slight modification of original
Thanks for your reply
For Sample Only I shown you Three Users
There are many more uses
If Manisha or Other User changes their password
how i will come to know what is their pawword
Admin can add user or add sheet or can change access for sheets
No any other user can add sheet or rename sheet
I am waiting for your reply
Atul
If you are asking about my code, no one (including the admin's) can know what a users password is (*).
An admin can Reset a users password to the default. Other than that, they cannot find out or change a user's password, only the user can change their own password.
(The default password is the users name, removed spaces followed by "pw".
"John Smith"'s default password is "JohnSmithpw")
(If you prefer a different structure for the default password, that can be changed.)
(*) My set-up is Excel VBA based, therefore it is not secure in any sense. A VBA savvy person (admin or not) could dig in and find a user's password if they wanted. But the system is designed to control access of willing parties not to thwart bad actors.
Hi again,
First of all I have to emphasise what mikerickson says - Excel is definitely not secure against malicious hackers, but is often adequately secure in a typical office environment.
The attached workbook is a slightly modified version of my original workbook. When a User is given access to "All" worksheets, this means all worksheets EXCEPT the "User Database" worksheet. Obviously, only Admin Users should have an "x" entered in their "User Database" cell.
The workbook is now protected - this means that worksheets cannot be added, deleted or renamed until the workbook is unprotected. The "User Database" worksheet (accessible only by Admin Users) contains buttons to toggle the workbook protection off and on so that Admin Users can add/delete/rename worksheets as required.
Hope this helps - please let me know how you get on.
Regards,
Greg M
Dear Mikerickson
Can you provide me same file with your formula and code
Dear Greg M
When I am loging with Manisha or sandeep ID
Then Blank Sheet Opens
I am unable to access your both files
It shows attached error
Please help
I am using user name as Manisha and password as 2326
Even I am unable to login with Admin user
Please help how to login with admin user
Atul
Hi again,
Remember, as I mentioned earlier:
Please note that (as per the example workbook you posted) the code determines the name of the User from the "Application.UserName" property, and not from the name entered on the login form.
The code can be altered to use the name entered on the login form if this is what you would prefer.
This means that it is not sufficient just to log in to the APPLICATION as (e.g.) Sandeep, the User must be logged in to the SYSTEM (i.e. to his/her computer) as Sandeep.
If you want to be able to log in to the Application as Sandeep while you are logged in to the System as Manisha, then it will be necessary to make some changes to the code.
Regards,
Greg M
Last edited by Greg M; 12-06-2014 at 07:57 AM. Reason: Minor change
There is a workbook attached to post #3. When you open the workbook, it will prompt you for a username and password.
You can use "admin" "admin"
or "Manisha" "Manishapw"
or "Sandeep" "Sandeeppw"
Dear Mr. mikerickson
I tried with your admin admin
and sandeep sandeeppw
Sheet doesnt open
with manisha its open but i cant see any other sheet
where i could see 2 sheets as per i described in post1
please help i depend on you only
Atul
How can I right code which will avoid checking of computer login
If you don't want the users to have to log-in, Greg's approach is the way to go.
Dear Mr. Greg , Mr. Mikerickson
Thanks for make me understand this
What you said
I need exact by that way only if user logged in computer with any username
then also when he will enter username in login form then file should open
But I am unable to change code as I dont have that much knowledge
Please help me to that and provide file with change in code
So I can add users in User Database
and asign file names in vb code to that user
Waiting for your reply
Atul
Dear Mikerickson
As per your file post 3
it is usefull for me
But please help me to make the same in my provided sheet
So I can use it and understant how it works for me
Because when I used your file
I am not able to give rights for specific new user
Please help
Atul
The sheet that you provided is locked and I can't access it.
Dear Mr. Mikerickson
I am realy sorry
I was totaly forgot to remove password from that file
Please remeber that this sheet I am going to share with password
I need to give userwise rights for different sheets
PFA file
Waiting for your reply
Atul
I've downloaded the file and opened it.
The Open routine tried to unprotect the active sheet, but failed. And when I tried to unprotect the locked sheets with the password in the open event, that failed.
But what really puzzles me is that you already have a log-in form in that workbook.
I can add my user form to your workbook, but are you asking for the data from your log-in form to be integrated into a protection scheme? or are you asking for a replacement for your form?
No Sir
you can use your Log in Form and remove mine
I already removed password
If incase there is any password
then password is ''evershine''
or ''123'' or ''502700''
You can use my login form also but I am not able to code for that
Just I need solution for this by any way
Waiting for your reply
Atul
The attached workbook has three users
admin (password: admin)
Manisha (password: Manishapw)
Sandeep (passowrd: Sandeeppw)
Manisha can only view two sheets (plus the welcome sheet)
Sandeep can view all sheets.
Manisha and Sandeep can change their passwords, the admin cannot (admin can reset their passwords to the defaults)
admin can add users and change the sheets they have access to.
Dealing with the sheet passwords, I'll leave to you. If you go with this system, I'd suggest leaving all the worksheets unprotected.
I apologize for Late Reply
Thanks for your Efforts Sir
you have made it as I was Expecting
Great Job
Now I am trying to copy your code and Form in another filehttp://www.excelforum.com/newreply.php?do=newreply&p=3928197&noquote=1
and make New one for another company
If any problem will come then I will ask your help
Thanks again
Atul
I am not able to do the same even after coping all your code in my file
I am not able to attach my file here as file size is more than 4 mb
Please help me
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks