Michael,
Go to the
VB Editor and double click the ThisWorkbook module.
You will find 2 drop down boxes at the top. From the left select Workbook.
You should now find
Private Sub Workbook_Open()
*your code goes here*
End Sub
Write your code in here.
Your code might look something like this
(Make sure that the welcome worksheet is the first sheet)
Dim pswrd as String, x as Long
pswrd = InputBox("Enter password","Password")
If pswrd = "mypassword" ' select your own password
For x = 2 to ActiveWorkbook.Worksheets.Count
Sheets(x).Visible = True
Next
End If
Worksheets("Welcome").Visible = xlVeryHidden
This will unhide all worksheets and hide the welcome sheet
Now in the same module from the right drop down select BeforeClose
You should now have
Private Sub Workbook_BeforeClose(Cancel As Boolean)
*your code goes here*
End Sub
This code might look somethink like this
Dim x as Long
Sheets(1).Visible = True
For x = 2 to ActiveWorkbook.Worksheets.Count
Sheets(x).Visible = xlVeryHidden
Next
The xlVeryHidden sets it so that the user can't unhide the sheet from the
Format menu.
Amend the code the to hide and to unhide specific sheets.
just add variations on these 2 lines and replace the x with either the
sheet index number or the sheet name (in quotes)
Sheets(x).Visible = xlVeryHidden
Sheets(x).Visible = True
You can get real cleaver and use different passwords to unhide just certain
sheets.
Once done - select VBAProjectProperties from the VBE Tools menu and protect
the project. Now your users can't see the code.
steve
"Michael M" wrote in message
...
I am a bit confused. Here is the behavior I want. When the
user opens a workbook, he/she is prompted for a password.
If the password is given, all sheets are visible and
editable. If the password is not given, workbook still
opens, but some sheets are hidden and those that are
visible are read-only. How would I accomplish this and if
a macro is involved, how do I prevent someone from
diabling the macro to subvert this check?
TIA for the help.
Michael
-----Original Message-----
Michael,
One trend that has been suggested a few times is to have
a single worksheet
with instructions to the user(s).
In a Workbook_Close event all sheets except this one are
xlveryhidden.
In the Workbook_Open event all sheets are made visible
and the instruction
worksheet is hidden. You can add password input here to
restrict what
happens. You can even set it up to show selected
worksheets by password
used.
As a final step the VB project is password protected to
prevent anyone from
seeing the code.
steve
"Michael Monteiro" wrote in
message
...
Is it possible to hide a worksheet from a particular set
of users? I was thinking about password protecting my
workbook. If the password is supplied, the workbook
opens
in edit mode. If the password is not supplied, it opens
in
read-only mode, but a couple of worksheets are hidden
from
view and therefore not accessible. How can I do this?
Furthermore, if I can do it, how do I prevent a user
from
subverting by disabling macros?
.