You could use the sheets' _activate events for this - to only display the required sheet, but I've found that by holding down the mouse on the sheet's name tab, the sheet can be viewed until releasing the mouse click again, when the _activate code finally fires - so that method's not particularly secure (not that anything's really secure in Office............)
I suggest setting the visibility of all but sheet1, to "XLVeryHidden", each time Sheet1's selected, in order to properly hide the other sheets.
To achieve this, put the following into Sheet1's _Activate event:
Code:
Private Sub Worksheet_Activate()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Sheets
If sht.Name <> "Sheet1" Then
sht.Visible = xlSheetVeryHidden
End If
Next
Then, in design mode, put 3 ActiveX Option buttons onto sheet 1, perhaps within a group box with some text explaining to click the option button to see the sheet etc etc.
Make the caption of the first "Select Sheet2", that of the second "Select Sheet3", and that of the third "Select Sheet4"
Finally, code the 3 buttons' _click events thus:
Code:
Private Sub OptionButton1_Click()
Dim txt As String
txt = InputBox("Password for sheet2, please", "PASSWORD REQUIRED")
If txt = "pword2" Then
With Sheets("Sheet2")
.Visible = True
.Activate
End With
End If
End Sub
Private Sub OptionButton2_Click()
Dim txt As String
txt = InputBox("Password for sheet2, please", "PASSWORD REQUIRED")
If txt = "pword3" Then
With Sheets("Sheet3")
.Visible = True
.Activate
End With
End If
End Sub
Private Sub OptionButton3_Click()
Dim txt As String
txt = InputBox("Password for sheet2, please", "PASSWORD REQUIRED")
If txt = "pword4" Then
With Sheets("Sheet4")
.Visible = True
.Activate
End With
End If
End Sub
Take the sheet out of design mode, click into sheet1 (to hide the others) and you should be done.
You could, of course, add a message box to each bit of code, if the user gets the password wrong etc, but I'm sure you can work that out!
NB don't forget to password protect your actual VBA project, otherwise users can get into there, and view the passwords for each sheet.
To protect the project, in the VBA explorer, right-click the project name, select "Project properties", select the "Protection" tab, and lock the project, along with entering a password.
