Very Hidden Password Protect Worksheets

KrisKiel

New Member
Joined
Feb 16, 2019
Messages
26
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.
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,649
Office Version
365
Platform
Windows
Hi. I was going to have a look at this for you; do you still need help with it?
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,840
Office Version
2019
Platform
Windows
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
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,649
Office Version
365
Platform
Windows
..... 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!
 

KrisKiel

New Member
Joined
Feb 16, 2019
Messages
26
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
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,649
Office Version
365
Platform
Windows
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
 

KrisKiel

New Member
Joined
Feb 16, 2019
Messages
26
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?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,060
Office Version
365
Platform
Windows
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:

KrisKiel

New Member
Joined
Feb 16, 2019
Messages
26
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?

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
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
[/FONT]
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,060
Office Version
365
Platform
Windows
Yes - you could safely make it the first line of the macro
 

Watch MrExcel Video

Forum statistics

Threads
1,099,754
Messages
5,470,581
Members
406,707
Latest member
drkjz

This Week's Hot Topics

Top