Results 1 to 5 of 5

Thread: Show hidden sheet and columns based on user

  1. #1

    Show hidden sheet and columns based on user



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi everybody, I am new at the forum and also in VBA. I would like to obtain help on the following: I have a document with several tables located one on each sheet. Now I would like to update the info from that tables but several teams have to do it. I have one sheet that contains the table name, the fields and the team in charge to update that field on that table, resulting that maybe several teams have to view one table however not the same field. So what I would like to do, to avoid errors, is for the teams can only view the table and columns that they need to update only.

    I have managed, looking trough the web, to create a document with user and password and depending on the user it only shows the sheets that the user should see. However I do not know how to make also the columns to be hidden according to user. Do you have any idea how can I do this? Thanks!!!

    I am attaching a zip file with two documents: "Zzz" is the document with user and password, "Table Names" is the excel document with the sheet with reference to the table names and fields.
    Attached Files Attached Files

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Hi mari_hitz.

    I'm afraid your code is going to need some further tweaks if you need this to be secure.
    To see what I mean, try these two things:
    1. Open the file, and enable macros. Put the wrong password in. When Excel says Access Denied, click on OK. THen when the messagebox prompting whether changes should be saved comes up, click CANCEL. THe file remains open. An experienced user can then make the hidden sheets visible by pressing Alt F11 and then setting the Visible property of any hidden sheets to xlSheetVisible. Note that if you add the line Application.DisplayAlerts = false to you code just before the ThisWorkbook.Close line, then users won't get prompted to save, and so won't have a chance to cancel.
    2. Open the file, but don't enable macros. The Workbook_Open code won't fire, and users can again make any hidden sheets visible by pressing Alt + F11 and then setting the Visible property of any hidden sheets to xlSheetVisible. In fact, currently if you do this you see the User List sheet with everyone's passwords.


    To stop users manually unhiding sheets, you will need to have code that hides any sensitive sheets whenever the file is closed, and then appply a password to the structure of the workbook so they can't manually unhide sheets via the Alt + F11 route I mention above.

    How sensitive will the information be in this file? Or is it just that you don't want non-administrators to break anything?

    To hide columns according to user, basically you just instruct VBA which ones to show depending on who the user is. I can help you with code for this (I have code from several projects I can share) but need to hear back from you first about the above.

    Regards,

    Jeff

  3. #3
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    You could check out my PassWord form
    Attached Files Attached Files
    Hope that helps

    Roy

  4. #4
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    royUK: This looks great. Note that if you put in a nonsense name that's not in the picklist, it thows an error, at which point you can cancel out of the macro and then have access to the file.

  5. #5
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    Simple fix, change the ComboBox MatchEntry Property to True

    Edit, I've amended the example
    Last edited by royUK; 2013-04-11 at 06:44 PM.
    Hope that helps

    Roy

Posting Permissions

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