As long as the people using the workbook are 'honest' and all you're
interested in doing is keeping users from accidentally altering someone
else's data or using the wrong sheet, then things are fine. But remember
that the encryption of the password for both workbook and worksheet
protection is very weak and easily cracked - so it's not much for providing
true 'security' type protection.
You could put code in each worksheet's _Activate() event to prompt for its
password, and if the proper password isn't given, leave the sheet locked up.
Another person posting a question about this kind of thing had such a deal
set up: when you activated a sheet, it first hid all of the columns on that
sheet and requested the password. If the proper password wasn't given, then
the columns remained hidden (and the sheet remained protected), but if the
proper password was given, then the columns were made visible and sheet
protection was removed. The sheet's _Deactivate() event handler was set up
to always put the sheet back into protected state with the columns hidden
again.
Here's a link to their initial posting that shows the code they used in the
two worksheet events:
http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.excel.misc&mid=509614b1-4e94-49e6-b95f-4da09fdbbed5
You can get around this either of two ways but I'll add the warning that Excel's
internal security is quite weak and passwords can be broken.
1. Have workbook_open code that checks to see who is the log-in user and open
just that sheet with all others veryhidden or a blank(dummy) sheet if users
disable macros.
2. Each user has his own password. The workbook opens with a blank(dummy) sheet
and an InputBox for user to enter his password which opens his sheet only.
No password to open the workbook, just code to make a user's sheet visible.
In the Thisworkbook Module....................
Private Sub Workbook_Open()
Dim pword As String
Select Case Environ("Username")
'if a login is not used and each user has his own sheet password change to
'pword = InputBox("Enter Your Password")
'Select Case pword
Case Is = "Gord": Sheets("Gordsheet").Visible = True
Sheets("Dummy").Visible = False
Case Is = "Pete": Sheets("Petesheet").Visible = True
Sheets("Dummy").Visible = False
End Select
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
Sheets("Dummy").Visible = xlSheetVisible
For Each sht In ActiveWorkbook.Sheets
If sht.Name <> "Dummy" Then
sht.Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
ThisWorkbook.Save
End Sub
You would protect the project so's prying eyes cannot see the code and
passwords.
You may also want as administrator to be able to open all sheets for editing.
Add these lines to the Workbook_Open code above End Select
Case Is = "Admin"
For n = 1 To Sheets.Count
Sheets(n).Visible = True
Next n
Gord Dibben MS Excel MVP
On Tue, 27 Nov 2007 11:15:02 -0800, Kristi <Kri...@discussions.microsoft.com>
wrote:
To get to where you need to be for the solution that Gord has offered,
right-click on the Excel icon immediately to the left of the word "File" in
the main Excel menu toolbar and again, choose [View Code] from that popup
list and copy/paste his code if you choose to go that route.
Jerry's second post explained how to do that.
Which way would you like to go? Jerry's method or mine?
If mine..............
Log-in username with a single password for all sheets which only you know or a
separate password for each sheet which only the user knows?
Send me a sample workbook by email with a list of some usernames or sheet
passwords and I'll see what I can come up with.
Change the AT and DOT for my email address.
Gord
On Tue, 27 Nov 2007 13:19:00 -0800, Kristi <Kri...@discussions.microsoft.com>
We may need you so don't stray too far<g>
Gord
1. You must have at least one sheet as you mention.
2. Kristi can leave a 36 pt. message in the middle of this sheet stating "You
have disabled macros, rendering this workbook unusable. Reopen with macros
enabled"
Gord
An update on this............Kristi mailed me the workbook and we got it set up
using the method of hiding sheets except for "Dummy" and user enters a password
to unhide just the user sheet.
Kristi was pleased with this.
Gord