Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Very Hidden Password Protect Worksheets

  1. #1
    New Member
    Join Date
    Feb 2019
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Very Hidden Password Protect Worksheets

    To start, I have no idea how to write or run macros. I know that I want to have 37 of the 39 sheets in a file I made to be VeryHidden so they are as protected as possible when the workbook is opened, with a prompt to enter a password to reveal the sheets.

    I've seen macros that I guess have worked in this manner for others but I was unable to successfully adapt them to what I need. The two sheets that I would like visible when the are titled "Instructions" and PEAK Triangle".

    Any help with this would be so greatly appreciated.

  2. #2
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,496
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Very Hidden Password Protect Worksheets

    Hi. I was going to have a look at this for you; do you still need help with it?
    Sykes
    Windows 10 / XL 2016

  3. #3
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    4,857
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Very Hidden Password Protect Worksheets

    Hi,
    If it is your intention to allow users on a corporate network to access the workbook & display worksheet that is relevant to them, then I would suggest that you do this by using their network username - You can do this by creating a table of authorised users & which avoids need for passwords.

    Dave

  4. #4
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,496
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Very Hidden Password Protect Worksheets

    ..... decided to get on with it anyway!

    As you've not successfully run code before, I'll try and give you step-by-step instructions...

    Each copy of Microsoft Office (of which Excel is a part) comes with accessible VBA (Visual Basic for Applications) code - if you know where to find it.
    This VBA opens up a whole new world of possibilities to a user who wants to do more with Excel (and Word etc) such as automate tasks, build a more "clever" programme, allow buttons, drop-downs & other controls to work etc.
    There's an absolute mass of help available out there - of which Mr Excel is just one example (but a very, very good one).

    I've written a few simple lines of code for you, which should achieve what you're requesting. Hopefully we can get it running for you, and start you on your way.

    The first thing I suggest, is to make a copy of your workbook, and test the code out on that; this way, if there are any mess-ups, your valuable data won't get trashed! If all goes well, incorporate the changes into your live workbook; this is always good advice, whenever you're trying new things - particularly when messing about with VBA, as the ever-handy "Undo" button doesn't work when you've used code to make changes.
    In order to tell the Excel Application that there's code to run in your workbook, you need to save it with a different file extension, which nowdays (in all but the oldest versions of Office) is ".xlsm".
    Do a "save As" from your current workbook, but select "Excel Macro-Enabled Workbook (*.xlsm)" from the drop-down. Call it either the same as your current workbook, or rename it - up to you.

    When you open this new file, it'll look exactly the same.

    Now we'll look at the code window:
    Right-click any sheet's name tab, and select "View Code." This will open the VBA interface. Bit scary, but don't worry - as you're only working in a copy of your treasured workbook, whatever you do doesn't matter too much, if it all goes pear-shaped, just shut it all down, delete the file, and start a fresh copy.

    If you look up the right-hand pane - toward the top, you should see "VBA Project" followed by the name of your file. It'll probably already be expanded - allowing you to see the names of the worksheets in brackets, if not, click the + to expand the project.

    One of the reasons your previous attempts weren't successful, may have been that your weren't placing the code in the correct place; depending upon what you want to do, the code needs to be placed in the pertinent area.
    What we want to achieve, is to run some code automatically - when the workbook first opens, so we need to put it in the "ThisWorkbook" code module; to access this, double-click the "ThisWorkbook" tab in the VBA Project - it'll probably be right at the bottom of the list of sheets - below the names of all your 39 sheets.
    Now, the larger pane on the right, will probably be blank, with two dropdowns at the top.
    From the LH dropdown, select "Workbook" and from the RH one, select "Open".
    You should see this:
    Code:
    Private Sub Workbook_Open()
    
    
    End Sub
    ....this is where Excel looks when it opens an xslm file, in case the coder wants any code run on start-up.
    As we want something to happen when your users open this file, we'll put our code in here.
    It's OK to just copy & paste into here, so if you copy this:
    Code:
    On Error GoTo mess
    
    Dim sht As Worksheet
    Dim inpt As String
    
    
    For Each sht In Me.Worksheets
        If sht.Name <> "Instructions" And sht.Name <> "PEAK Triangle" Then
            sht.Visible = xlSheetVeryHidden
        End If
    Next
    
    If InputBox("Please enter the password, to reveal all of the worksheets.", "Password, please.") = "Abracadabra" Then
        For Each sht In Me.Worksheets
            sht.Visible = xlSheetVisible
        Next
    End If
    Exit Sub
    
    mess: MsgBox "There's a problem with the Workbook_Open code!", vbCritical, "Code Error!"
    On Error GoTo 0
    ......and paste it in between the two lines already in your code pane, the whole lot should look, thus:
    Code:
    Private Sub Workbook_Open()
    
    On Error GoTo mess
    
    Dim sht As Worksheet
    Dim inpt As String
    
    
    For Each sht In Me.Worksheets
        If sht.Name <> "Instructions" And sht.Name <> "PEAK Triangle" Then
            sht.Visible = xlSheetVeryHidden
        End If
    Next
    
    If InputBox("Please enter the password, to reveal all of the worksheets.", "Password, please.") = "Abracadabra" Then
        For Each sht In Me.Worksheets
            sht.Visible = xlSheetVisible
        Next
    End If
    Exit Sub
    
    mess: MsgBox "There's a problem with the Workbook_Open code!", vbCritical, "Code Error!"
    On Error GoTo 0
    End Sub
    Now, save the workbook - either from the VBA Project window, or from the workbook itself - in the normal way.

    We're actually ready to go, but there are a couple of other considerations:
    1. As you'll see from the code, we've hard-coded the password into the code, so anyone who knows about VBA can go in and see the password. They can also go in there, and make a right ol' mess of all your hard work! To overcome this, you'll probably want to password-protect the VBA project. To do so, go back to your project in the LH pane, right-click the "VBA Project" followed by the name of your file (at the top of all the folders & worksheet names), and select "VBA Project Properties..." from the dropdown menu.
    It'll probably open on the "General" tab at the top, but you want the "Protection" tab, instead. Select the "Lock project for viewing" checkbox, and enter a password to lock your VBA. Probably better to make it a different password to the one used to unhide your sheets...!
    2. Most modern Excel applications default to macro security being on, which means that code won't automatically run - at least without a prompt to the user. You'll therefore need to give consideration to educating your users - to allow macros when prompted, or even to changing their macro security settings (this is a personal setting for each user), so that code can run automatically each time. With this comes an added risk, of course, so it'll be up to you how you decide to manage this; also, if you're operating in a network environment, you're well-advised to discuss all of this with your network administrators, as changing security setting may violate company policy. Just sayin'!
    For macro security, go to File/Options/then (depending upon your version of excel) probably "Trust Center" (hate that spelling!)

    If you now shut everything down, and re-open the file, hopefully, you'll get your prompt. Anything but the correct password being entered (including pressing "Cancel" or using the cross to close the inputbox down) should result in only the two worksheets being visible.
    This is probably obvious, but you'll need to replace my "Abracadabra" password in the code, with one of your choice. Also, the password will be case-sensitive.

    Let's see how you get on.
    Good luck!
    Sykes
    Windows 10 / XL 2016

  5. #5
    New Member
    Join Date
    Feb 2019
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Very Hidden Password Protect Worksheets

    This worked perfectly! Thank you so very much!

    And thank you too for the instructions. It was all incredibly helpful. I am definitely seeing the boundless utility of VBA - I just added a button to export some of the sheets as a PDF, so cool.

    'Trust Centre' would be a much better spelling haha

  6. #6
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,496
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Very Hidden Password Protect Worksheets

    Pleasure, and thanks for the feedback.
    Good to hear that you've run your first successful bit of code - and that you've already got the taste, and written some more!
    All the best.
    Sykes
    Sykes
    Windows 10 / XL 2016

  7. #7
    New Member
    Join Date
    Feb 2019
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Very Hidden Password Protect Worksheets

    So I took your advice about needing to educate users. I found a way to just force macros to activate when the file opens - much easier.

    It works correctly when I open it but it gets... jumpy? It looks like itís spazzing out when it opens and then again when I enter the password and the sheets are becoming visible. Like I said, it works fine but it looks crazy. Any ideas how to smoothen it out?

  8. #8
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,413
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Very Hidden Password Protect Worksheets

    To prevent screen flicker ...

    try inserting this line
    Code:
    Application.ScreenUpdating = False
    immediately below
    Code:
    On Error GoTo mess
    And, just in case you (or others) are wondering, its value is automatically reset to True when the macro ends
    Last edited by Yongle; Jun 13th, 2019 at 12:40 AM.

  9. #9
    New Member
    Join Date
    Feb 2019
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Very Hidden Password Protect Worksheets

    That worked wonders. No screen flicker there.

    It still flickers with the code to force enable macros (obviously because it's a separate sub). Would putting that line of code anywhere stop that too?

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'Step 1: Declare your variables
    Dim ws As Worksheet
    'Step 2: Unhide the Starting Sheet
    Sheets("START").Visible = xlSheetVisible
    'Step 3: Start looping through all worksheets
    For Each ws In ThisWorkbook.Worksheets
    'Step 4: Check each worksheet name
    If ws.Name <> "START" Then
    'Step 5: Hide the sheet
    ws.Visible = xlVeryHidden
    End If
    'Step 6: Loop to next worksheet
    Next ws
    'Step 7: Save the workbook
    ActiveWorkbook.Save
    End Sub

  10. #10
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,413
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Very Hidden Password Protect Worksheets

    Yes - you could safely make it the first line of the macro

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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