..... 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!
