Protect formulas but still allow formatting, etc.?

[Steve]

I'm trying to protect all the cells in a spreadsheet with formulas in
them (don't mind the user seeing the formulas, just don't want him
changing them), have done so using the following code (compliments of
this group, thanks!):

Sub lock_cells()
Set mySheets = ActiveWorkbook.Sheets
For Each s In mySheets
If s.Name <> "Information Summary" Then
s.Activate
ActiveSheet.Unprotect
Cells.Locked = False
Selection.SpecialCells(xlCellTypeFormulas, 23).Locked =True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End If
Next s
End Sub

Problem is, some functionality goes away -- e.g., I have some sheets
where I have grouped rows/columns, and once the sheet is protected the
user cannot collapse/expand these groups. Also, on a protected sheet
the user cannot change any formatting -- can't bold or indent, change
column width or font color/size, etc.

Is there a way to simply lock only the cells with formulas but still
allow the user to do anything else on the sheet(s)? Something like
lock the cells without protecting the sheet?

 

[Tom Ogilvy]

For the outlining, from help on EnableOutlining

EnableOutlining Property
True if outlining symbols are enabled when user-interface-only protection is
turned on. Read/write Boolean.
Remarks
This property applies to each worksheet and isn't saved with the worksheet
or session.

Example
This example enables outlining symbols on a protected worksheet.

ActiveSheet.EnableOutlining = True
ActiveSheet.Protect contents:=True, userInterfaceOnly:=True

 

[Steve]

Beautiful, Tom. Thanks.

I suppose as far as being able to do simple format changes (bold an
entry, indent a cell, change font color, etc.) I am still out of luck?
I've done more searching in this group and it appears that besides the
Data Validation option that some have mentioned (and which I do not
want to use b/c of its shortcomings), I am stuck with all the other
limitations.

If anyone knows of other code solutions similar to what Tom just
suggested re: Outlining, I'd much appreciate it.

 

[Tom Ogilvy]

If you have Excel 2002 or later, you have a lot more options with sheet
protection. Of couse if someone using the workbook will have an older
workbook or your version is older, then that doesn't help much.

These are some of the options found under worksheet level protection in
those newer versions:

expression.Protect(Password, DrawingObjects, Contents, Scenarios,
UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns,
AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows,
AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows,
AllowSorting, AllowFiltering, AllowUsingPivotTables)

 

[Steve]

Thanks a million! Haven't tried them yet, but if I'm interpreting
correctly it looks like I'll be able to do most if not all that I want.
You rock.