Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bradenton, FL
    Posts
    64
    Thanks
    5
    Thanked 1 Time in 1 Post

    Permitting users to edit only their protected tabbed sheet in a shared excel 2013 workbook

    I use Office 365 Business Premium to create and maintain a single tabbed workbook that tracks my team's performance data. The first tab is labelled "Compiled" and the rest (25-30) are labelled with each team member's name. The compilation pulls the results from each individual sheet's column totals, presenting an overall team summary. I want to share this workbook with my team, perhaps providing links to my Google Docs account. I do not want to rely on OneDrive. I want to protect the workbook and tabbed sheets as follows:

    1. Each team member can access and enter data on only his or her own tabbed sheet.
    2. Within that member's sheet, the format, column headings and SUM formulas that total columns cannot be changed.
    3. The compile tab is for my eyes only, as I bring the overall team report to our meetings.
    4. I retain ownership and control of the workbook.

    I'm not experienced with the whole sharing, protection and permission variables that seem to be the fundamental tools available. Is this possible and if so is there a good step-by-step source you can direct me to?
    Win 10 Pro 64bit on Dell XPS12 w/ Intel Core I5, 256gb mSATA, 4gb RAM. Also self-built Asus Z97 Desktop PC, Core I3, Kingston 96gb SSD, Toshiba 128 SSD, 8gb Corsair DDR4 1600, CM 692II Case, Radeon R7 250X, Samsung 23" Mon w/HDTV tuner.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Haybarn,

    Here is a workbook with 6 sheets: A splash screen that the workbook will initially open to (the rest are hidden) and prompt for a username and password, Compilation sheet, 3 users sheets, and a credential sheet for username and password maintenance.

    If the workbook is opened with macros disabled (ex. holding shift key when double clicking on file), the only sheet visible is the splash screen and the user will be prompted to enable macros and try again. The user cannot do anything else at this point.

    haybarn1.png

    When the workbook is opened with macros enabled, the user will be presented with a form to enter both the user name and password. If incorrect, the user will get a message that the credentials do not match the database

    haybarn2.png

    If the credentials are correct, the following actions will occur:

    If master account (Haybarn)
    1. All sheets will become visible and unlocked.
    2. The splash screen and form will be hidden

    I general user account (jsmith, bwilliams, mbrown)
    1. Only the user's sheet will be visible and locked according to how you have formatted the sheet with locked cells
    2. The splash screen and form will be hidden

    haybarn3.png

    When closing the workbook, even if the user has saved, the workbook book will be saved in the following manner in preparation of the next time it is opened.
    1. All sheets will be hidden and password protection re-applied
    2. The splash screen will be the only sheet visible
    3. The VB project is protected

    Credential maintenance:

    On the credential sheet there are 3 columns. Column A is the user and should match the tab name exactly. I did not include a sheet for Haybarn but it is optional. Column B is the username and column C is the password. When adding a new sheet to the workbook, also add an entry to the Credential sheet. Again, make sure the user matches the tab name while the username and password can be anything (case sensitive). You can change any user's username and password at any time. Only you (the maser account) will be able to see the credential sheet. If copying a sheet to this project, it is best to place after then compilation sheet but before the Credential and splash sheet in alphabetical order. Although it makes no difference to the code, it is easier to manage and easier on the eye. Any inactive employees can be deactivated by removing their account from the credential sheet. Their tab sheet can be deleted or left in place and always hidden except to the master account.

    haybarn5.png

    When testing the sheet, use the credential in the image above. The password to unlock both the sheets and the VB project is "Maudibe" w/o the quotes. If you are interested, please let me know what you want your passwords to be and I will make the simple adjustments. You can have up to 50 users but that can be easily adjusted.

    HTH,
    Maud
    Attached Files Attached Files

  3. #3
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bradenton, FL
    Posts
    64
    Thanks
    5
    Thanked 1 Time in 1 Post
    Maudibe - I appreciate this solution and will keep it for other situations. For now, I found that a group of 30 volunteers having mixed computer skills can access a Google Doc version of my workbook via an emailed link. It is not a perfect solution, but so far it looks like it will be good enough for data entry on their personal worksheet tabs, coupled with automatic recalc of team totals and auto save. They do not have to use gmail, or have google accounts for access. Thanks again.
    Win 10 Pro 64bit on Dell XPS12 w/ Intel Core I5, 256gb mSATA, 4gb RAM. Also self-built Asus Z97 Desktop PC, Core I3, Kingston 96gb SSD, Toshiba 128 SSD, 8gb Corsair DDR4 1600, CM 692II Case, Radeon R7 250X, Samsung 23" Mon w/HDTV tuner.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •