Protecting worksheet

[Art Cummings]

Morning all,

I'm trying to understand the purpose of the "Allow all users of this
worksheet to:" in the protect sheet menu. It implies that a worksheet can
be protected in a granular fashion. For example I could allow the user to
insert columns, add data, format rows, but not delete columns or rows. When
I try selecting these options and then save the sheet, i'm always prompted
to unprotect before any data can be entered.

I'm trying to understand the purpose of the "Allow all users of this
worksheet to:" check boxes. If it's necessary to unprotect before data can
be added, it's unclear what function these check boxes assume.

Any help is appreciated.

Thanks

Art

 

[Dave Peterson]

xl2002 added this level of granular protection.

So if you're opening this workbook on earlier versions of excel, you will need
to unprotect the worksheet before doing any of those things.

Entering/changing the values/formulas in locked cells in protected sheets does
require more.

Each cell can be locked or unlocked. But this lockedness doesn't do much until
the worksheet is protected. In xl2003 menus, you can select the range to lock
(or unlock) and use:

Format|Cells|Protection tab

to toggle this setting. (Do this with the worksheet unprotected.)

Then you should be able to edit the cells.

If you're using xl2002+, you should be able to protect the worksheet and allow
any user to insert rows/columns (or any of the other items in that dialog).

But you'll only be able to delete entire columns/rows if that entire column/row
has unlocked cells. (Don't you see a warning message that states this when it
fails?)

 

[Art Cummings]

Thanks Dave,

What I see is a message that says the sheet must be unprotected before any
changes can be made. I'm using excel 2003.

Under "Allow all users of this worksheet to:"

I select all of these except Delete columns, Delete rows. It seems this
should allow the user to do everything except those 2 actions, but as soon
as the user tries to make any modifications, they are asked for the password
to unprotect the sheet.

This raises the question: is there a way to protect the sheet without a
password?

Select locked cells
Select unlocked cells
Format cells
Format columns
Format rows
Insert columns
Insert Rows
Insert Hyperlinks
DELETE COLUMNS
DELETE ROWS
Sort
Use Auto Filter
Use Pivot Table report
Edit objects
Edit scenarios

 

[Dave Peterson]

You don't have to use a password when you protect a sheet. Just click the Ok
button on that dialog.

You may want to provide some details of what you've tried that has failed.

 

[Art Cummings]

Morning all,

Dave the message i'm getting is "The cell or chart you are trying to change
is protected and therefore read-only"

What i'd like to happen is that the user is able to modify data and add data
but they are not able to delete columns or rows. I'd like for this to be
transparent to them and that they only see a message when they try to delete
cols or rows. The check boxes i previously listed, imply that there is
granularity for controlling specific actions on a spreadsheet, such as
format cell, insert row etc..., What i'm seeing is that the ENTIRE sheet is
protect and the check boxes don't seem to offer any granularity.

I've got 4 users that are access the spreadsheet and someone removed 3
columns by mistake. I'd like to prevent this in the future.

Thanks

Art

 

[Dave Peterson]

I'm confused.

Did you want to allow users to delete columns or do you want to stop them from
deleting columns?

If you want the users to be able to delete columns, then make sure all the cells
in the columns that can be deleted are unlocked. And check that option to allow
them to delete columns.

If you don't want them to delete any columns, then don't check that option.

If you want them to delete some columns, but not others, then make sure that you
check that option--and you unlock all the cells in the columns that are ok to
delete. But make sure you keep at least one cell locked in each of the columns
that should not be deleted.

Morning all,

Dave the message i'm getting is "The cell or chart you are trying to change
is protected and therefore read-only"

What i'd like to happen is that the user is able to modify data and add data
but they are not able to delete columns or rows. I'd like for this to be
transparent to them and that they only see a message when they try to delete
cols or rows. The check boxes i previously listed, imply that there is
granularity for controlling specific actions on a spreadsheet, such as
format cell, insert row etc..., What i'm seeing is that the ENTIRE sheet is
protect and the check boxes don't seem to offer any granularity.

I've got 4 users that are access the spreadsheet and someone removed 3
columns by mistake. I'd like to prevent this in the future.

Thanks

Art

 

[Art Cummings]

Thanks Dave,

I don't want them to delete columns. I've tried selecting all the check
boxes with the exception of delete col, row but i'm still prevented from
making any changes in the spreadsheet until it's unprotected.

Thanks

Art

 

[Dave Peterson]

Did you unlock the cells that they could change?

Thanks Dave,

I don't want them to delete columns. I've tried selecting all the check
boxes with the exception of delete col, row but i'm still prevented from
making any changes in the spreadsheet until it's unprotected.

Thanks

Art

 

[Art Cummings]

I believe they are unlocked is there a way to tell

 

[Dave Peterson]

Select the cells you want unlocked.
Format|Cells|Protection tab|make sure Locked is unchecked.

I believe they are unlocked is there a way to tell

 

[Art Cummings]

Dave there is no Format|Cells|Protection tab

There is only Data|Protection and the options are:
Unprotect
Allow user to edit ranges
Protect Workbook
Protect & Share Workbook

The problem is that i only want to remove permission to delete rows or cols.
I don't want to prohibit data entry. I'm not sure this is possible but the
check boxes imply that it is.

Thanks

Art

 

[Dave Peterson]

If you're using xl2003 menus, there's a Format option on the worksheet menu bar.

If you're using xl2007 ribbon, use the Home tab, cells group, Format icon.

In any version, select the range and rightclick on that selection and choose
Format Cells.

Dave there is no Format|Cells|Protection tab

There is only Data|Protection and the options are:
Unprotect
Allow user to edit ranges
Protect Workbook
Protect & Share Workbook

The problem is that i only want to remove permission to delete rows or cols.
I don't want to prohibit data entry. I'm not sure this is possible but the
check boxes imply that it is.

Thanks

Art