Access to view worksheets with different passwords

[Guest]

I know how to password protect a workbook at the workbook level for viewing
and editing. I would like to be able to set up the workbook so that it always
defaults to the same tab, and once inside, users can only view the other tabs
in the workbook if they have a separate password unique to that tab.

Example: The workbook has tabs: "Group Sales Results", "Mark's Sales
Results", and "Sandy's Sales Results", where group results are a roll-up of
Mark and Sandy's results. How can I structure the workbook so that Mark and
Sandy use a common password to open the workbook, but once they open it to
the group results tab, they can only click on their own tab and view the data
if they have the password to that worksheet?

 

[Dave Peterson]

If that data is really private and shouldn't be seen by anyone else, don't put
it in excel.

The worksheet/workbook protection (via tools|protection) is very weak--I use it
as a way to stop users from overwriting formulas.

There's code posted/linked to everyday that shows how to unprotect this stuff.

 

[Guest]

The information is not really all that private. If someone in this
organization wanted to, they could find it all out on network drives now. I'm
compiling the data for the first time, and I'd just like to discourage team
members from looking at one another's results. Is it possible to set up the
passwords as I described for viewing only certain worksheets in the same
workbook? If so, how?

 

[Dave Peterson]

I think I'd create a helper workbook to open the password protected workbook.
Then I'd know that macros were enabled.

The only sheet in this workbook would have this in big red letters:

Please close this workbook and reopen--but enable macros
Please close this workbook and reopen--but enable macros
Please close this workbook and reopen--but enable macros
Please close this workbook and reopen--but enable macros
Please close this workbook and reopen--but enable macros
Please close this workbook and reopen--but enable macros
Please close this workbook and reopen--but enable macros


Then in a general module, put this code:

Option Explicit
Sub auto_open()

Workbooks.Open Filename:="c:\my documents\excel\book1.xls", _
Password:="hi"

ActiveWorkbook.RunAutoMacros xlAutoOpen

ThisWorkbook.Close savechanges:=False

End Sub

When the user opens this workbook, they'll either get a flash of that warning
screen (and then it'll disappear).

If they open it with macros disabled, then they'll see the message.

====
In the real workbook (book1.xls in my example), I'd create an "index"
worksheet.

Put a button from the Forms toolbar on that index sheet.

In a general module, put this code:

Option Explicit
Sub auto_open()

Dim wks As Worksheet
Dim oRow As Long

oRow = 1
With Worksheets("index")
.Buttons(1).Caption = "Select a cell with a sheet name" _
& vbLf & "And click me"
.Cells.Clear 'clean up all the data
.Range("a1").Value = "Worksheet Name"
For Each wks In ThisWorkbook.Worksheets
If wks.Name = .Name Then
'do nothing
Else
oRow = oRow + 1
.Cells(oRow, "A").Value = "'" & wks.Name
wks.Visible = xlSheetVeryHidden
End If
Next wks
End With

End Sub

Sub ShowSheet()

Dim Pwd As String
Dim testWks As Worksheet
Dim UserEntry As String
Dim okToShow As Boolean

Set testWks = Nothing
On Error Resume Next
Set testWks = ThisWorkbook.Worksheets(ActiveCell.Value)
On Error GoTo 0

If testWks Is Nothing Then
MsgBox "Not a valid sheet!"
Exit Sub
End If

Select Case LCase(testWks.Name)
Case Is = "sheet1": Pwd = "hi1"
Case Is = "sheet2": Pwd = "hi2"
Case Else: Pwd = ""
End Select

okToShow = True
If Pwd = "" Then
'do nothing
Else
UserEntry = InputBox(prompt:="What's the password, Kenny?")
If UserEntry <> Pwd Then
okToShow = False
End If
End If

If okToShow Then
With testWks
.Visible = xlSheetVisible
.Select
End With
Else
MsgBox "Incorrect password"
End If
End Sub

The auto_open sub will hide all the sheets (except for Index). And it'll
refresh the list in that Index sheet.

Then the user selects a cell, clicks the button and off you go.



Make sure you protect your code in both projects.

Select your project in the VBE and then
Tools|VBAProject Properties|Protection tab

If you don't protect the project, all the passwords will be too easy to see.

(But protection of your code is not safe either. It can be broken easier than
worksheet protection.)

Adjust your passwords and filenames accordingly.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm