Passwords for Multiple Users of Workbook

[Brandy]

This is what I really want: 1 workbook with 7 worksheets. each worksheet has
it's unique "owner". i would like like the owner of that worksheet to have a
password that will allow them to open their worksheet and make modifications
as needed. each owner can ONLY open their worksheet. then 1 "general"
password that would allow an administrator to open ALL of the worksheets and
review the information as necessary.

is there a way to do all that???

 

[Eduardo]

hI,
In the worksheet you want the owner to open copy the code as follow, to do
that go to the tab right click in the mouse, view code and copy it there,
change the password to your needs, you will have to do the same with each
sheet with different passwords

Private Sub Worksheet_Activate()
Dim strPassword As String
On Error Resume Next
Me.Protect Password:="MANAGER"
Me.Columns.Hidden = True

strPassword = InputBox("Enter password to access DATA sheet")

If strPassword = "" Then
ActiveSheet.Visible = False
Worksheets("Menu").Select
Exit Sub
ElseIf strPassword <> "MANAGER" Then
MsgBox "Password Incorrect "
ActiveSheet.Visible = False
Worksheets("Menu").Select
Exit Sub
Else
Me.Unprotect Password:="MANAGER"
Me.Columns.Hidden = False
End If
Range("a1").Select

On Error GoTo 0
End Sub

Private Sub Worksheet_Deactivate()
On Error Resume Next
Me.Columns.Hidden = True
On Error GoTo 0
End Sub


If this help please click yes, thanks

 

[Gord Dibben]

Sample code.......................

Note: the following is contingent upon users enabling macros.

If they don't only the "Dummy" sheet will be visible with a large message
stating "By disabling macros you have rendered this workbook unusuable.
Please close and re-open with macros enabled"

I assume you are on a network(LAN) with users logging into the system.

I would set it up so that whichever user's login name is flagged, all sheets
except that user would be hidden.

No password to open the workbook or sheet protection, just code to make a
user's sheet visible.

In the Thisworkbook Module....................

Private Sub Workbook_Open()
Dim pword As String
On Error GoTo endit
Select Case Environ("Username")

'if a login is not used change to
'pword = InputBox("Enter Your Password")
'Select Case pword

Case Is = "Gord": Sheets("Gordsheet").Visible = True
Case Is = "Pete": Sheets("Petesheet").Visible = True
End Select
Sheets("Dummy").Visible = False
Exit Sub
endit:
MsgBox "Incorrect Password"
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

To allow you to see all sheets and edit them.

In a general module...............

Sub UnHideAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Visible = True
Next n
Application.ScreenUpdating = True
End Sub

Naturally you want all this code invisible to the users.

Right-click on the workbook/project in VBE and select VBAProject Properties
and "Lock project for viewing"

Enter a password.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

If the data on those 6 other worksheets should be kept from the other users,
then excel isn't the application for you.

Almost any suggestion you get will depend on a macro (and macros can be disable)
and worksheet/workbook protection (and those are easily broken).

Your data will be more secure if you create 7 different workbooks (one worksheet
each) and only share each workbook with trusted co-workers.

 

[Brandy]

Dave -- Do you have a suggestion of an Office product that could work for
this? All 7 users need to be using the same "workbook" or program. But we
don't want Joe being able to see Sally's information that she enters. But ...
we need the "manager" to be able to view all information entered by the
employees.

The way it is setup now is that each of the 7 employees have their own
worksheet on an Excel Workbook. But everyone can look at each other's info
and modify it ... if they wanted to be deceptive!

 

[Brandy]

Eduardo - I am not clear on what section the user of the worksheet would need
to change to their personal password??

And by someone entering MANAGER, they would be able to view ALL worksheets
.... correct???

 

[Dave Peterson]

Using excel and separate workbooks is one approach.

Maybe you can have this kind of security with a real database program? I don't
use Access, so I don't know if that program of the Office suite has that level
of security.

Maybe someone will chime in with a better application.