+ Reply to Thread
Results 1 to 8 of 8

Display sheets based on password

  1. #1
    Registered User
    Join Date
    08-19-2013
    Location
    Abbotsford, Canada
    MS-Off Ver
    Excel 2010
    Posts
    2

    Display sheets based on password

    I am developing a workbook to be used for recording employee hours worked during each pay period. There is one worksheet tab per employee, which will be completed by the employee's supervisor each pay period and then the entire spreadsheet will be sent to payroll. There are different supervisors for each program and each supervisor only needs access to the employees' worksheets who are in their program.

    What I envision is a single workbook that has all employee worksheets in it but when it is opened by each supervisor, they only see the employees in their programs and all other employee worksheets are hidden. This would require multiple passwords for the workbook and depending on which password is entered, only certain worksheet tabs are visible. All other tabs would be hidden. I only see a way to assign one password for locking or hiding tabs. I need to assign multiple passwords with different access rights.

    I don't want to create multiple workbooks, just one that can be updated and reissued whenever changes need to be made (i.e. new departments added, new employees, pay period end dates changed, etc.).

    Is there anyway to do this?

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Display sheets based on password

    Assume I am speaking very slowly and deliberately:
    "Do not trust confidential information to be secured in an Excel workbook that is accessed by multiple users."

    Find another way to gather and consolidate your information. If you must use Excel, engage a competent, professional Excel consultant who will help you get what you need without using a shared workbook.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    08-19-2013
    Location
    Abbotsford, Canada
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Display sheets based on password

    Thanks for your response. All supervisors are cleared to access the information in this spreadsheet. The idea of locking the tabs has to do with simplicity and error prevention.

    Most of the users are not very computer literate and I don't want to confuse them by presenting them with a spreadsheet with 100 tabs when only 10 of the tabs apply to them. It will make their jobs easier if they don't have to scroll through all the tabs to find their employees, plus it will prevent them from accidentally completing the wrong tabs.

    Also, the individual sheets are laid out in an identical fashion to the online payroll timesheet system we will eventually transition the supervisors to using. We decided to first introduce them to a spreadsheet to acclimate them to the idea of entering timesheets electronically (it's being done manually on paper right now), rather than give them access to the live payroll database. This will allow them to learn and make mistakes without any severe repercussions.

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Display sheets based on password

    Hi CanadianGuy,

    As Ron stated, be very careful as Excel is not the tightest security platform.

    Here is a sample from a Microsoft Excel MVP Chris Smith.

    Please read carefully the opening directions on the dashboard sheet and the rest should be pretty easy.

    If this does not get you what you want, in your browser you can type...

    site: Excelforum.com password for different sheets

    ...and you should get many hits to look through with other examples...
    Attached Files Attached Files
    HTH
    Regards, Jeff

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Display sheets based on password

    Hi,

    One way would be to have a very hidden sheet, i.e. that couldn't be unhidden from the application only from the VBE. In that sheet you would have a list of supervisor names and their passwords and for each supervisor a list of their sheets.

    All other sheets would be similarly hidden apart from an Introduction sheet. This would contain a single drop down cell which would allow the user to select a supervisors name. On selection a pop up box would prompt for the password. This would be compared with their allotted pw and if correct the relevant sheets would be unhidden.

    That's one way although without workbook sharing, and as Ron implies I would steer well clear of that for reasons we won't go into here, only one person can update it at a time.

    The other way is to have a workbook for every supervisor and have a consolidating workbook that would gather all the data from the individual workbooks when a macro was set running. You'd end up with the same result but arguably it will be more flexible than having supervisors fighting over access to a single workbook!
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  6. #6
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Display sheets based on password

    In that case and in similar scenarios...
    I create structured, pre-populated workbook templates for each user to complete. Those templates are either collected electronically or saved into a common location. I then use a data harvesting template to consolidate the information and structure the aggregated data for upload to the final program.

    Some of the benefits of that approach are:
    • Each template only contains the information the specific user requires
    • Historical data can be used to pre-populate the new templates, reducing input time and errors
    • no single user can corrupt all of the workbooks and all of the data.
    • Users can submit their completed templates any time
    • Changes to the master template can be done once and new templates can be sent out any time using an automated process.

  7. #7
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Display sheets based on password

    @Ron

    Couldn't agree more. By far the best way of proceeding.

  8. #8
    Registered User
    Join Date
    08-15-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Display sheets based on password

    Hi jeffreybrown,

    Some error in your attachment, Check Modified file. Sorry for my bed English.
    Attached Files Attached Files
    Last edited by sanjaypalsjp; 08-22-2013 at 03:37 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. FIXED - MAcro to protect/unprotect sheets based on password in cell
    By pmcook1979 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-10-2012, 04:28 AM
  2. Show/hide sheets on file open based on user password
    By allar in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-10-2011, 12:41 PM
  3. hide / unhide separate sheets based on password
    By spudinsane in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-27-2011, 04:48 AM
  4. vba to hide / unhide separate sheets based on password (bis)
    By sigeg in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-26-2011, 05:23 PM
  5. View limited sheets in workbook based on logon/password protect
    By Nicole Seibert in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-06-2006, 05:50 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1