Hi,

I am working on a Excel 2003 document on a network drive, wo which several
people have access. Is it possible in Excel to give different peoples
different rights regarding:
- being able to see a certain sheet of column;
- beig able to change certain sheets of colums.

I know I can use read-only or I can even make passwords to prevent people
from changing sheets of even columns. But is it possible that people need
another password to see/open a certain sheet?

In other words...some data in de file should just be accessable for certain
people.

Thanks in advance!

Marieke

The basic answer is yes - with reservation. You can set things up using VBA
code (macros) so that just a single sheet is visible when the workbook is
opened, then require the user to enter their name or password and based on
that entry, set other sheets to be visible or other custom protection on
them. As a matter of fact, if you know the username they will open the file
with (part of their Windows logon), you could base it on that.
The reservation? Any of this type of protection can be bypassed by the
knowledgable user who has the determination to take a few extra steps after
opening the file.

"mariekek5" wrote:

Hi,

I am working on a Excel 2003 document on a network drive, wo which several
people have access. Is it possible in Excel to give different peoples
different rights regarding:
- being able to see a certain sheet of column;
- beig able to change certain sheets of colums.

I know I can use read-only or I can even make passwords to prevent people
from changing sheets of even columns. But is it possible that people need
another password to see/open a certain sheet?

In other words...some data in de file should just be accessable for certain
people.

Thanks in advance!

Marieke

Thanks a lot for the quick reply!

Could you help me a bit further with the VBA code (macros)...?! Or maybe a
website where its explained properly?



"JLatham" wrote:

The basic answer is yes - with reservation. You can set things up using VBA
code (macros) so that just a single sheet is visible when the workbook is
opened, then require the user to enter their name or password and based on
that entry, set other sheets to be visible or other custom protection on
them. As a matter of fact, if you know the username they will open the file
with (part of their Windows logon), you could base it on that.
The reservation? Any of this type of protection can be bypassed by the
knowledgable user who has the determination to take a few extra steps after
opening the file.

"mariekek5" wrote:

Hi,

I am working on a Excel 2003 document on a network drive, wo which several
people have access. Is it possible in Excel to give different peoples
different rights regarding:
- being able to see a certain sheet of column;
- beig able to change certain sheets of colums.

I know I can use read-only or I can even make passwords to prevent people
from changing sheets of even columns. But is it possible that people need
another password to see/open a certain sheet?

In other words...some data in de file should just be accessable for certain
people.

Thanks in advance!

Marieke

Here are the basics of what you'd need. Essentially we need to work with the
Workbook_Open() event and with one sheet's Worksheet_Change() event. The
first routine is pretty generic - make all worksheets except one invisible to
the user. The second routine handles details - setting up access to the
sheets/areas of the other worksheets based on whatever was typed into A1 on
the one visible sheet.

To see how this works, open a new workbook with at least 3 sheets in it.
Rename one of them SignIn that's our 'special'/always visible sheet where
your users would enter their password into cell A1. Leave the other two
sheets named Sheet2 and Sheet3 so that this sample code will work once it's
in place.

Here's the first piece you need - the Workbook_Open event code. To put it
into the proper place, right-click on the Excel icon immediately to the left
of the word "File" in the Excel menu toolbar and choose [View Code] from the
list and copy and paste this code into that module:

Private Sub Workbook_Open()
'this simply makes all worksheets, EXCEPT
'the one named SignIn "very hidden" which
'means they don't even show up in the list
'of worksheets available to be made visible
'in the Format | Sheet | Unhide list

Dim anyWS As Worksheet
'make certain that the sign in sheet
'is visible and no left over password in it
ThisWorkbook.Worksheets("SignIn").Visible = xlSheetVisible
ThisWorkbook.Worksheets("SignIn").Range("A1") = ""
For Each anyWS In ThisWorkbook.Worksheets
If anyWS.Name < "SignIn" Then
anyWS.Visible = xlSheetVeryHidden
End If
Next
Set anyWS = Nothing
End Sub

Next we need some code to react to an entry made in cell A1 of the SignIn
sheet. Right-click on the name tab for the SignIn sheet and again choose
[View Code] from the popup list. Copy the code below and paste it into the
code module presented to you. This is just minimal code to give you an idea
of how things could work for you. We can get into specifics later if need
be, but it may be that these are the only examples you need to make it work
in the real workbook. Here's the Worksheet_Change() event code:

Private Sub Worksheet_Change(ByVal Target As Range)
'we are only interested in changes made to
'cell A1 on this sheet
'entries are treated like passwords -
'i.e., they are case sensitive (Bill not same as bill)
'any keyboard character is valid,
'no leading or trailing white space,
'and first character cannot be single quote mark
'
'"ralPH\*49 golf5h0t!" is valid
'but " ralPH\*49 golf5h0t!" is not
'and "'ralPH\*49 golf5h0t!" is not

Dim passwordEntered As String
Dim anyWS As Worksheet
'start by making all other sheets very hidden again
For Each anyWS In ThisWorkbook.Worksheets
If anyWS.Name < "SignIn" Then
anyWS.Visible = xlSheetVeryHidden
End If
Next
Set anyWS = Nothing
If Target.Address < "$A$1" Then
Exit Sub
End If
passwordEntered = Trim(Target.Text)

Select Case passwordEntered
Case Is = "ralph\*49 golf5h0t!"
'make specific sheets for this
'user visible, and/or set up
'cell protection/access on the
'sheets you do make available
'to this user.
ThisWorkbook.Worksheets("Sheet2").Visible = _
xlSheetVisible

Case Is = "password#2"
'make specific sheets for this
'user visible, and/or set up
'cell protection/access on the
'sheets you do make available
'to this user.
ThisWorkbook.Worksheets("Sheet3").Visible = _
xlSheetVisible

Case Is = "password#3"
'make specific sheets for this
'user visible, and/or set up
'cell protection/access on the
'sheets you do make available
'to this user.
ThisWorkbook.Worksheets("Sheet2").Visible = _
xlSheetVisible
ThisWorkbook.Worksheets("Sheet3").Visible = _
xlSheetVisible

Case Is = "password#4"
'make specific sheets for this
'user visible, and/or set up
'cell protection/access on the
'sheets you do make available
'to this user.

'doesn't do anything right now

Case Else
'not a recognized/valid password
MsgBox "Your entry is not a valid password. Check spelling and
punctuation.", _
vbOKOnly + vbExclamation, "Password Not Recognized"
End Select

End Sub


If you need more detailed assistance with things like using passwords on the
worksheets or making specific ranges on the sheets available/unavailable
based on the user who logged in, might be better if you got in touch with me
via email (remove spaces to make valid email address) at
Help From @ JLathamSite .com


"mariekek5" wrote:

Thanks a lot for the quick reply!

Could you help me a bit further with the VBA code (macros)...?! Or maybe a
website where its explained properly?



"JLatham" wrote:

The basic answer is yes - with reservation. You can set things up using VBA
code (macros) so that just a single sheet is visible when the workbook is
opened, then require the user to enter their name or password and based on
that entry, set other sheets to be visible or other custom protection on
them. As a matter of fact, if you know the username they will open the file
with (part of their Windows logon), you could base it on that.
The reservation? Any of this type of protection can be bypassed by the
knowledgable user who has the determination to take a few extra steps after
opening the file.

"mariekek5" wrote:

Hi,

I am working on a Excel 2003 document on a network drive, wo which several
people have access. Is it possible in Excel to give different peoples
different rights regarding:
- being able to see a certain sheet of column;
- beig able to change certain sheets of colums.

I know I can use read-only or I can even make passwords to prevent people
from changing sheets of even columns. But is it possible that people need
another password to see/open a certain sheet?

In other words...some data in de file should just be accessable for certain
people.

Thanks in advance!

Marieke