I am trying to password protect a set of formulas so that users cannot change
them. For some reason I cannot get this to work with this specific file but
can with others (following steps from Excel Help works with other files but
not this one).

The sheet will not let me change any cell regardless of the range I choose.
If I choose a cell within my protected range it will ask for the
range-specific password, otherwise it tells me the sheet is protected (needs
the sheet-specific password).

What am I missing? Would using CSEs or macros matter?

Thanks in advance,

MB

Select all the sheet, right click,
"Format Cells"
"Protection" tab,
untick the "locked".
Then select the range you need and lock only the specific cells.
Then proceed to "protection" command.

Hope it helps!


"Matt Beardsley" wrote:

I am trying to password protect a set of formulas so that users cannot change
them. For some reason I cannot get this to work with this specific file but
can with others (following steps from Excel Help works with other files but
not this one).

The sheet will not let me change any cell regardless of the range I choose.
If I choose a cell within my protected range it will ask for the
range-specific password, otherwise it tells me the sheet is protected (needs
the sheet-specific password).

What am I missing? Would using CSEs or macros matter?

Thanks in advance,

MB

Excellent. Thanks for the help.

Now another issue: I want to able to sort the sheet with a macro, but with
the range protected it gives me an error (Run Time Error '1004': Sort method
of Range class failed). How do I get around this? I need to protect these
formulas but more importantly need to sort the data fairly often. Can't sort
manually as Sort is gayed out due to protection.

I guess have the macro unprotect, sort, and reprotect? Not sure how to do
this or if that is the right way...

Thanks again,

MB

"ice" wrote:

Select all the sheet, right click,
"Format Cells"
"Protection" tab,
untick the "locked".
Then select the range you need and lock only the specific cells.
Then proceed to "protection" command.

Hope it helps!


"Matt Beardsley" wrote:

I am trying to password protect a set of formulas so that users cannot change
them. For some reason I cannot get this to work with this specific file but
can with others (following steps from Excel Help works with other files but
not this one).

The sheet will not let me change any cell regardless of the range I choose.
If I choose a cell within my protected range it will ask for the
range-specific password, otherwise it tells me the sheet is protected (needs
the sheet-specific password).

What am I missing? Would using CSEs or macros matter?

Thanks in advance,

MB

Sorry for the delay but I just logged in.

I know, I had the same problem.

You have to "edit" the specific macro and add manually the password.
See the example I have for you:
The password is in (_) and is next to the words protect and unprotect.

Hope it helps!


End With
Sheets("PRICE LIST").Select
ActiveSheet.Unprotect (123456)
Selection.AutoFilter Field:=7, Criteria1:="NEW*"
ActiveWindow.SmallScroll Down:=-6
ActiveSheet.Protect (123456), DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowDeletingColumns:=True, AllowDeletingRows:=True,
AllowSorting:=True _
, AllowFiltering:=True, AllowUsingPivotTables:=True
End Sub

"Matt Beardsley" wrote:

Excellent. Thanks for the help.

Now another issue: I want to able to sort the sheet with a macro, but with
the range protected it gives me an error (Run Time Error '1004': Sort method
of Range class failed). How do I get around this? I need to protect these
formulas but more importantly need to sort the data fairly often. Can't sort
manually as Sort is gayed out due to protection.

I guess have the macro unprotect, sort, and reprotect? Not sure how to do
this or if that is the right way...

Thanks again,

MB

"ice" wrote:

Select all the sheet, right click,
"Format Cells"
"Protection" tab,
untick the "locked".
Then select the range you need and lock only the specific cells.
Then proceed to "protection" command.

Hope it helps!


"Matt Beardsley" wrote:

I am trying to password protect a set of formulas so that users cannot change
them. For some reason I cannot get this to work with this specific file but
can with others (following steps from Excel Help works with other files but
not this one).

The sheet will not let me change any cell regardless of the range I choose.
If I choose a cell within my protected range it will ask for the
range-specific password, otherwise it tells me the sheet is protected (needs
the sheet-specific password).

What am I missing? Would using CSEs or macros matter?

Thanks in advance,

MB