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...