+ Reply to Thread
Results 1 to 12 of 12

User name and password to unhide worksheets during Excel file is opened

  1. #1
    Forum Contributor
    Join Date
    10-03-2012
    Location
    USA
    MS-Off Ver
    2010
    Posts
    496

    User name and password to unhide worksheets during Excel file is opened

    Hello,
    I like to have user to entry user name and password in order to unhide the specified worksheets during Excel file is opened.

    Thanks

  2. #2
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: User name and password to unhide worksheets during Excel file is opened

    Hi, this should do what you want.
    Usernames & passwords are stored on sheet4
    Attached Files Attached Files
    Regards
    Sean

    Please add to my reputation if you think i helped
    (click on the star below the post)
    Mark threads as "Solved" if you have your answer
    (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code:
    [code] Your code here [code]
    Please supply a workbook containing example Data:
    It makes its easier to answer your problem & saves time!

  3. #3
    Forum Contributor
    Join Date
    10-03-2012
    Location
    USA
    MS-Off Ver
    2010
    Posts
    496

    Re: User name and password to unhide worksheets during Excel file is opened

    Hi Sean Thomas,
    It works very good. There is slight problem, when I save the, the sheet 2-4 would be closed because
    once a while I click "save" to save my work and continue to work on. Is there a way to hide the sheets,
    when the file is closed? If the file has not been saved, click "closed" will ask to save a file, then the sheet2
    will be hidden and save, then close the file.

    Is that possibe or better to save the file without hiding the sheets, even when the file is closed. When the file
    is reopened, then vba will hide the sheet2, 3 and 4; then start at sheet1 to enter user anme and password?
    I like this approach, what do you think? I hope that is possible.

    Thanks of your help!
    Last edited by Rocky2013; 10-20-2013 at 07:46 PM.

  4. #4
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: User name and password to unhide worksheets during Excel file is opened

    Hi, if you save the file without hiding the sheets, when you open the file again the other sheets will be visible until macros are enabled. So if the user doesn't enable macros they will be able to access the other sheets. Then there is no point having a password.

  5. #5
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: User name and password to unhide worksheets during Excel file is opened

    Take a look at the attached. Passwords are case sensitive and accept punctuation.
    There are four users:
    admin (password: "admin" (no quotes))
    Bob (password: "Bob's Password")
    Mary (password: "Mary's Password")
    Dave (password: "Dave's Password")

    putting the admin username and password will show all the sheets, including HiddenSheet, which has the username/password/permissions data.
    Note that Mary and Dave have a permitted sheet in common.

    Put this code in a normal module.
    Please Login or Register  to view this content.
    This in the code module for Sheet1 (the one with the username/password verification)
    Please Login or Register  to view this content.
    and this in the ThisWorkbook code module
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by mikerickson; 10-21-2013 at 05:03 AM.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  6. #6
    Forum Contributor
    Join Date
    10-03-2012
    Location
    USA
    MS-Off Ver
    2010
    Posts
    496

    Re: User name and password to unhide worksheets during Excel file is opened

    Hi mikerickson,
    This works. I play around little bit, so I sign in as bob. It shows two sheets, sheet1 and bobsheet. I right click bobsheet, a menu pop-up; I am be able to
    unhide other 4 sheets. Is there a way to hide all the sheets that are not allow bob to access. If bob inserts his own sheet, he should have access to unhide
    and hide his own sheet.

    I like to see if sheet1 may be hided after bob sign-in. Sheet1 will show again during log-in.


    Hi Sean Thomas,
    Is that possible to "save" the file will not log-out for input name and password? How about use "closed" the file, the user will either save or not save the file.
    Either command will closed the files.


    Thanks

  7. #7
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: User name and password to unhide worksheets during Excel file is opened

    At the end of sub TestPasswordMatch, change this line of code

    Please Login or Register  to view this content.
    The question of Bob getting access to a sheet that he adds can be addressed by requiring that there be some "admin". The Workbook_SheetActivate would check if admin has permission to access sh.Name. If so, do nothing. If admin does not have access, that indicates that sh is a new sheet and sh.Name should be added to both admin's permissions and the permissions of the current user. That describes how to code it, but I don't have time to do the coding right now. The other question is "what to do if an un-verified user adds a sheet while looking at the log-in sheet"?
    Attached Files Attached Files
    Last edited by mikerickson; 10-22-2013 at 09:45 AM.

  8. #8
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: User name and password to unhide worksheets during Excel file is opened

    Hi Rocky,
    sorry its not very clear what you are asking?

  9. #9
    Forum Contributor
    Join Date
    10-03-2012
    Location
    USA
    MS-Off Ver
    2010
    Posts
    496

    Re: User name and password to unhide worksheets during Excel file is opened

    Hi Sean Thomas,
    If I click "save" to update my work, Shee2, 3 and 4 will be hidden. To go back those sheets, I would need to
    enter the user name and password again. I usually click "save" few times to update the spread sheet. To
    avoid this problem, since I always need to close the file and the program when I finish. If I use "Closed"
    with "save" or "no save" to hide the sheet2, 3 and 4 prior to close the file. Seems this way will eliminate the
    repeated user name and password entry.

    Hi mikerickson,
    Before log-in, I would like to disable the right click or grey out all commands. One reason is to prevent adding new sheet, change sheet number, or even look at the code to find the user name and password. I could add password for protect viewing the codes in the VBA project property.

    Thanks all!!

  10. #10
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: User name and password to unhide worksheets during Excel file is opened

    Hi Rocky,
    It is set up to hide sheets when saving/closing. Reason for this is if you save the file with the sheets open and then carry on working but then decide to close the file without saving changes, it will save the file with the sheets open and therefore when you open the file again the sheets will be visible. Thus rendering the whole exercise useless.

    There might be a way around it, but it would involve saving the latest one as a temporary file, and then opening the original saved file and saving with sheets closed then close the temporary file.
    This will need a bit of playing around with of which im afraid I don't have the time for now.

  11. #11
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: User name and password to unhide worksheets during Excel file is opened

    Here's a version which accommodates the user adding sheets.
    It does not accommodate the user changing sheet names. Its a question of convenience.
    As is, the hidden permissions sheet is based on tab names. It could be switched to using code names, but the admin would have to realize that and act appropriately when giving permission to users for specific sheets.
    A code name based permissions sheet will be proof against the user changing sheet names.
    Attached Files Attached Files

  12. #12
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: User name and password to unhide worksheets during Excel file is opened

    Hi Rocky,
    I have changed the code so that it does the following.
    1.need password to open other sheets.
    2.If you save changes when closing it will close other sheets and save then close.
    3.if you don't make any changes it will just close.
    4.if you save workbook with other sheets open, then close it closes other sheets, saves and closes.
    5.if you save workbook with other sheets open, make changes but choose to close without saving changes, it saves current file as temp file, opens the last saved file, closes other sheets, saves & closes original file, and then closes temp file.

    This should do what you want.
    Code to open worksheets is in sheet1
    code to close worksheets is in thisworkbook and module 1

+ 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. Automatic close Help file (.chm) which was opened with Excel user form
    By leecs in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-24-2013, 06:02 AM
  2. Switchboard in Excel. Password to hide and unhide sheets. User form hangs
    By Lifeseeker in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-21-2011, 06:27 PM
  3. Replies: 0
    Last Post: 04-20-2006, 11:45 PM
  4. Replies: 2
    Last Post: 08-28-2005, 12:05 PM
  5. How to recover worksheets lost when I opened an Excel file in Wor.
    By Steviek1 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-23-2005, 07:06 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