+ Reply to Thread
Results 1 to 6 of 6

Is there a setting to ensure a user can't reference a hidden sheet?

  1. #1
    Registered User
    Join Date
    01-05-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    12

    Is there a setting to ensure a user can't reference a hidden sheet?

    I'm having a problem where users are able to enter the name of a hidden sheet and point to a cell to make hidden data visible on a sheet with unlocked cells. Is it possible to prevent this? I thought hiding it would do the trick, but I guess not.

    For example, I have three worksheets: Summary, East, and West. I hide West because it has salary information for particular employees. When I type any kind of cell reference into Summary that points to the hidden West sheet (e.g., =West!A1, =West!A2), that data appears in the sheet, which allows users to see other people's salary information.

    Any help would be greatly appreciated.

  2. #2
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Is there a setting to ensure a user can't reference a hidden sheet?

    MS Office is meant to share data, not for securing it. No matter what you do that information can be gotten to by someone with a little time/desire and Google. Your best bet is to export out just the summary sheet with values in place of formulas into its own file (either Excel format or an alternative like as a PDF).
    Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.

    "I am here to help, not do it for people" -Me

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Is there a setting to ensure a user can't reference a hidden sheet?

    There is a "very hidden" option, in the developer tab you select properties then at the bottom across from visible is -1 - slSheetVisible and in clicking the dropdown you will see 2 - xlSheetVeryHidden
    The very hidden sheets will NOT show when right clicking on a tab for "unhide".

    But caveats, as Zer0Cool noted it can be unhidden by someone with a good excel skill set. If for example you use formulas that refer to the very hidden sheets then sheet names will appear in them. TO UNHIDE the very hidden sheets you need VBA.
    HTML Code: 
    Where "sheet2" is the name of the very hidden sheet.
    If you use this you better note the names of the sheets because if you hide them you need the names exactly to unhide them.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  4. #4
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Is there a setting to ensure a user can't reference a hidden sheet?

    Or someone could just Google "unhiding all sheets" and find a snippet of code to loop the sheets collection and unhide every sheet without knowing their names.

    The only way to keep information from someone in Excel is to not include it in the file, its that simple unfortunately. All of the "protections" offered in Excel are meant to prevent accidents, not secure a file.

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Is there a setting to ensure a user can't reference a hidden sheet?

    @Zer0Cool, if I wasn't clear enough I was trying to say you were right.

  6. #6
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Is there a setting to ensure a user can't reference a hidden sheet?

    @Sambo kid I'm with ya, I just wanted to make it clear to the OP that it wouldn't take much to overcome such an effort such as very hidden. As you basically stated it'd likely be more of an issue for the OP than for someone trying to get the info.

+ 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. [SOLVED] Reference zoom levels to user entry cells on same sheet
    By spyac in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-16-2018, 10:29 PM
  2. Ensure user fill up all necessary cells before proceed for saving
    By steveliew in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-22-2016, 10:02 PM
  3. [SOLVED] ensure user selects radio button before adding information
    By JamesT1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-27-2014, 10:00 AM
  4. Replies: 4
    Last Post: 10-20-2012, 07:41 PM
  5. Hidden sheet gets activaetd on cancelling the user form
    By Chetan Chauhan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-25-2012, 10:19 PM
  6. Replies: 2
    Last Post: 06-06-2006, 11:20 AM
  7. Ensure Addin is loaded properly by user - no double clicking
    By Rich in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-27-2005, 09:05 AM

Tags for this Thread

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