Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2010
    Posts
    27

    Unanswered: Hide tabs from specific user(s)

    I have a workbook which is working perfectly, and saving an immense of time now that it's working. So what does the boss want? Changes.

    I think it should be relatively simple, but I can't seem to figure it out.

    I have multiple tabs in a workbook... one tab for each person on the team, and a few for the manager which pulls through numbers from all the people on the team.

    It's working great, but now I'm told it needs greater privacy as they don't want every team member to be able to see everyone else's tab (holds nothing confidential, I don't see what the big deal is... but anyhoo).

    So, I would like to have a way for the boss to be able to access/see all tabs, and the other users to access/see only one tab.

    I don't need a great deal of security.. just to keep nosy eyes off other people's tabs. Can this be done either by limiting to username that the excel is licensed to.... or by putting in a password... or something? I'd like to keep it as user friendly as possible for everyone too.

    Any help would be greatly appreciated. Cheers in advance!

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    There really isn't a secure way to do this, so the solution is purely for aesthetic purposes. The solution below forces VBA to be enabled, because VBA code will be used to show the appropriate sheet to the user so, if macros are disabled, then they will not be able to view their sheets.

    As I see it, there are 3 parts to the solution -


    Initial Set-Up
    • The workbook needs to be set up with a generic sheet visible - one that all users can see. (This is because a workbook must always have at least one visible sheet).
    • All 'private' sheets are very hidden
    • The workbook structure needs to be protected.
    • The VBA project will also need to be password protected.
    Showing Sheets

    An event, such as a user opening the workbook or clicking on a button, can be used to show the appropriate sheet(s). You could detect the user's Windows ID or you could ask for a password for authentication, whichever you prefer.

    Saving

    You will also have to include a Workbook_BeforeSave() event handler. When a user tries to save, the "Initial-Set Up" state of the workbook needs to be temporarily restored before the workbook is actually saved. This is because whatever the workbook looks like when it is saved is how it will look when the next user opens it. [Note that a routine to hide sheets when the workbook is opened is not a good choice because a user could simply disable macros to see someone else's sheet]. Once the workbook is saved with its initial start-up structure, the structure of the workbook can be restored to how it was prior to saving so that it doesn't inconvenience the user.


    Again, this solution cannot be relied upon from a security perspective and can be easily bypassed by anyone who is more than a little curious.

    Hope that helps...

  3. #3
    Join Date
    Mar 2010
    Posts
    27
    That all sounds like it'd work fine.

    Now, the greater question... how do I code it? lol. I'm VERY novice when it comes to programming. If you can help me out with an example, I should be able to stumble through getting it up and running on my workbook.

    Also, a second question... would I be able to have 2 people in the workbook at the same time, only seeing their worksheets (along with the one general one)? Could I have 5 people on 5 different worksheets at the same time?

    And now that you mention it, there may be a much simpler solution if the above question isn't possible. I could have different spreadsheets for each person... allowing only their ID to see the sheets... including the boss seeing the 'master sheet' (for lack of a better term). Although I've got no clue how to code that either. lol.

    Any help is greatly appreciated!

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,
    Now, the greater question... how do I code it? lol. I'm VERY novice when it comes to programming. If you can help me out with an example, I should be able to stumble through getting it up and running on my workbook.
    Well, before we go to the effort of writing the code let's make sure it's what we want! Let's look at some outstanding points.
    would I be able to have 2 people in the workbook at the same time, only seeing their worksheets (along with the one general one)? Could I have 5 people on 5 different worksheets at the same time?
    The answer to this is... it depends. For a normal workbook you could have many users in the same workbook, each only viewing their own sheet, but only the 1st person that opened the workbook would have write access.

    To give multiple users simultaneous write access you would have to share the workbook. Sharing a workbook is something I avoid because it restricts what can be done with it (some functionality is lost) and comes with a myriad of other headaches.
    And now that you mention it, there may be a much simpler solution if the above question isn't possible. I could have different spreadsheets for each person... allowing only their ID to see the sheets... including the boss seeing the 'master sheet' (for lack of a better term). Although I've got no clue how to code that either. lol.
    I had assumed that this wasn't an option. To make sure I understand what you mean by "spreadsheet", you mean a different Excel workbook for each user? If this is an option then it's a far superior solution.

    Firstly no VBA would be required: you could simply add a different password to open each workbook which only the relevant person and administrators would know, so it is much simpler to implement.

    Secondly, password protection to open a workbook is much more secure than internal protection within a workbook (such as VBA project protection, workbook structure protection, sheet protection etc..), so it is better from a security perspective.

    If you can then go for this.

    Hope that helps...

  5. #5
    Join Date
    Mar 2010
    Posts
    27
    Right, I suppose you're right in that it's much easier to just password protect the workbook (and yes, you defined my use of spreadsheet correctly... I had meant to fix it originally... oops!).

    Just to clarify... if I have a different workbook for each person... will the boss still be able to pull data from each person's workbook to have data from everyone? Or will they be locked unless the boss knows the password?

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    How is the data pulled in? Formulas? When your boss updates links he would be prompted for each of the different passwords because the other workbooks would be closed. I imagine he wouldn't like that.

    To make your boss' life easier you could write a simple little routine which opens the workbooks for him. It really depends on how this data is being pulled in.

  7. #7
    Join Date
    Mar 2010
    Posts
    27
    Yeah, the data is pulled in by formulas... and no, the boss wouldn't like putting a password in for everyone.

    At the moment, every person has their own workbook, which the master workbook then pulls data from with formulas.

    Looks like it's back to idea #1. lol. I'm not all that worried about security. Essentially I'm looking to stop it from being easy to look at other people's data... but at the end of the day, if they're really wanting to see it and go around the protection, it's not that big of a deal.. we're not talking about super sensitive and private stuff.

    I was thinking I could just copy everyone's individual workbooks into the master one, then hide all the rest from everyone that doesn't need to see it. But people would need to have access to their specific sheet in the workbook to be able to update it... simultaneously.

    If you've got a simpler solution, I'm all ears.

    Thanks again!

  8. #8
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Well, you could write a little routine to open the other users' workbooks in the background when your boss wants to pull in the data. Provided the workbooks have been opened then (s)he won't be prompted for a password when (s)he updates links. Might be worth a shot?

  9. #9
    Join Date
    Mar 2010
    Posts
    6
    Hi you can try use LoginTrap.It’s prog can capture every login events by using iSight.It really good prog.

  10. #10
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    How would that program be of any use in this scenario, Willy01?

  11. #11
    Join Date
    Mar 2010
    Posts
    27
    Sorry for the delay in replying... been REALLY busy.

    Right... for now, the boss has said that perhaps the protection isn't necessary (after me indicating it couldn't be easily).

    So... for now, no changes required. HOwever, if that changes, I'm sure I'll be asking again how to code it. lol.

    Thanks for the help!

Posting Permissions

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