Security

[dwake]

I have a couple of questions. I want to protect a workbook, but grant access
to a person to edit one sheet in the workbook. I do not want him to have the
password. I can't seem to find a way to allow him write access without
unprotecting the workbook. I than have to protect each individual sheet, and
grant him access to write going that route.

I am also having problems opening a group and outline tab while a worksheet
is protected.

Anyway around these? Any advice would be appreciated.

 

[Paul C]

In the protection section there is the option to Allow Users to Edit Ranges.
You can set a range that an individual user (or network security group) can
edit without using a password.

You can set your protection to allow the use of the outlines, but it is not
one of the options when you protect a sheet. You need to set it up with an
Auto_Open macro

I use this.

Sub Auto_Open()

Count = ActiveWorkbook.Worksheets.Count

For A = 1 To Count
Sheets(A).Unprotect Password:="password"
With Sheets(A)
.Protect Password:="password", AllowFormattingCells:=True,
DrawingObjects:=False, userinterfaceonly:=True
.EnableOutlining = True
.EnableAutoFilter = True
End With
End If
Next A

End Sub

You will need to change the "password" to your sheet password.
Also since there are many different options for protection you may wnat to
protect your sheet with the marco recorder turned on so you get all of your
specific options.

These would replace the
..Protect Password:="password", AllowFormattingCells:=True,
DrawingObjects:=False, userinterfaceonly:=True

the Password:="password", will not be recorded and you must add this yourself

 

[dwake]

So my question now is, if the workbook is password protected, will a user
still be able to edit a defined range of cells and have the ability to save?
Or will I have to supply a password so the file does not have to be opened in
read only?

 

[Gord Dibben]

Why does the workbook have to password protected?

And do you mean password to open or just password to protect workbook?

You can protect all sheets.

On the one sheet, protect but allow edit ranges.

Just be aware that Excel's internal security is weak and sheet and workbook
passwords easily broken.


Gord Dibben MS Excel MVP

 

[dwake]

Just password to protect. I want people to be able to view the data I'm
providing, I just don't want people to accidentally change the data, and I
want one sheet where people have to edit to update the spreadsheet. This is
not confidential information, so I am not worried about people breaking into
it, I am just trying to avoid user caused errors by having it open for anyone
to edit.

I am also having trouble with that code that allows outlining while
worksheet protection is on.

 

[Gord Dibben]

In your original post you wanted one user(him) to be able to edit cells on
one worksheet but not the other sheets.

But now you mention "people" editing the "spreadsheet".

Spreadsheet is generally used as a synonym for "workbook" or file.

Please clarify who should be allowed to edit what.

And you want to be able to enable outlining on the one sheet or on all
sheets?

Do you want all users to be able to access the outlining function?

All this can be done while protecting the sheets but code has to be written
depending on who gets access to what.

i.e. one user to edit one sheet can be set up based on that user's login
name when the workbook is opened.

Also............you must think about how will you handle users who do not
enable macros when the workbook/file is opened. Else all this coding is for
naught.


Gord