Certain users can only access certain work sheets??

    Hi all,


    I have been Looking all over, but I can't find an answer to this challenge. We currently have different files for different Departments, so pulling a summary report is not possible without Opening each one of These files to update my summary page. Here my Question: I would like to combine alls these single files into one, is there a way to create a workbook with several work Sheets (A, B, C....) and then to protect them in a way, that only User A can see & Access Sheet A, User B can only see & Access Sheet B, User C can only see & Access Sheet C. Important is that User A can fully Access Sheet A but has no Access to the other Sheets, not even to look at the data on them.
    Of Course Administrator X should have Access to all Sheets. Also, could then a User have Access to several Sheets, eg. User D has Access to sheet D and E?


    Is that actually possible or am I expecting too much from Excel??

    It is possible but we would need more information. What data from which sheets do you want to copy from each department file and where in the summary file do you want to paste it? Please attach copies of the summary file and at least one of the department files. If possible, it is best to post the actual files rather than sample files. A solution that works with sample data most often will not work with the actual data. De-sensitize the data if necessary. Explain in detaill what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data. In the summary workbook, you will need a sheet with the user names with their corresponding password and sheet names to which they have access. We would also need the full path to the folder containing the department files and their file extension (xlsx, xlsm).

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    Hi Mumps,
    Thanks for your Reply. Sorry I did not post a file. Here it is. The summary sheet pulls only Information from the Dep1-10 Sheets to Display the totals for the entire Company. On the Dep1-10 Sheets, the HODs will track the attendance of their staff, and the totals in columns NG, NH and NI will be taken over to the summary sheet. On the Users sheet, I want to be able to Input the user names and Passwords.
    I want the different users only to be able to Access their work sheet (eg. User 1 -> Dep1 sheet). So when they open the Excel file, a User Form will open up for them to enter their user Name and Password and then only their Dep sheet will open. They cannot Access the others. The Admin of Course should be able to see and open all Dep Sheets. There are no other files, all are combined into this one file (we used to have Dep1, Dep2, Dep3,..... as separate files, but that doesn't allow me to pull the totals from them without Opening each and every one of them).


    I hope this explains it better. Thanks again for taking the time to help me in this Project.

    Try the attached file. The macros assume that there will not be more than one user with the same user name (for example, 2 Frank's) . It is also assumed that the sheet names will be identical to the department names. Please note that once a user has correctly entered a valid user name and password, they could have access to the macros and unhide any sheet they want which in effect bypasses the security. You can protect [SIZE=13px]your macros by protecting your VBA Project.[/SIZE]


    To protect your VBA Project. Do the following:
    -hold down the ALT key and press the F11 key to open the Visual Basic Editor
    -click on 'Tools' on the top menu
    -click 'VBAProject Properties'
    -click the 'Protection' tab
    -click the box to the left of 'Lock project for viewing' to put a check mark in it
    -enter your password and then confirm it and click 'OK'
    -close the VB Editor
    -save your workbook as a macro-enabled file and close it
    When you re-open the file, you will not be able to see the macros unless you enter the password. Keep in mind that this type of protection is not very strong and anyone who really wants to get at your macros can probably do it with a little research. I hope this helps.

    Files

    myexcelstar.xlsm 99.82 kB – 105 Downloads

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    Hi Mumps,


    thank you soooo much. This works perfectly!!!! Wonderful!!!!! Just two Questions:
    - When someone opens the file first, it opens in protective mode, asking to activate the Content before Opening the file. What it means is, the file opens up in Dep6 tab first with the instructions to "activate Content". Once I do that, then the UserForm Pops up. This means, the Person Opening the file will be able to read what is written on Dep6 sheet. Can this be changed so it opens in an empty sheet?? It happens only the first time when the program does not know that the file is safe.
    - Can the Password field be set so it Displays ****** upon entering the Password?


    Thanks again, you are a star!!

    Hi Mumps,


    thanks, works very well. I made a modification and was wondering if you could tell me if what I did is correct. In the file you sent me, Sheets were just hidden, so any user would have still be able to unhide them. What I did, in your Code, I changed ws.Visible = xlSheetHidden to ws.Visible = xlSheetVeryHidden.
    When I do this, the Option of unhiding does not even come up for the users (except the Admin). Is that the Right way of doing it?


    Thanks so much

    [SIZE=12px]It sure is. Very hidden [/SIZE][SIZE=12px]sheets do not appear in the Unhide [/SIZE][SIZE=12px]dialog box. They can be made visible only by using a VBA code.[/SIZE]

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    Hi Mumps,
    what I realized now, when someone opens the file for the first time on a different Computer, it opens fully minimized, with all Sheets visible. I guess this is because of the security Questions to enable the Content and to activate the Content. Can this be avoided? I don't want anyone to see the Sheets that are not meant for them.


    Thanks so much

    Check out my PasswordForm. Here's the latest version.


    There's three sheets that you can use for different users, you can rename them or add more. If you add more then the code will need amending. Log in as Manager with the password secret and you can change passwords and users.

    Try the attached version.


    [USER="75"]royUK[/USER]
    When I opened your file and clicked the "Validate" button leaving the textboxes blank, I got an error message. When I clicked "Debug", I was able to view all the macros and also unhide all the sheets which gave me access to the ID's and passwords.

    Files

    myexcelstar.xlsm 103.49 kB – 95 Downloads

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    My pleasure. :) Glad it worked out.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    Obviously you can access the VBA, in a working version this would be protected with a password.


    The UserForm itself would also have the red X cancelled so that the user cannot close it that way.

    [USER="75"]royUK[/USER]
    Thank you so much for the update. :)

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login