Utilizng full AutoFilter features while specific cells are protect

[Travis]

I have three worksheets going on with dynamic content between all three
worksheets (data is entered on sheet and then shows up on another sheet). I
manually protected individuals cells that contain these formulas so a person
could not inadvertently "screw things up." (Format/Protection/then clicked
"Locked"). I then activated the protection feature for the entire worksheet
(Tools/Protection/Protect Sheet).

On the top of each worksheet sheet I activated the AutoFilter function so
that each column in that worksheet can be filtered. When I click on the
AutoFilter drop down menu I am able to filter each column by the lower
options (All, Top 10, Custom, Blanks, Non Blanks) but I cannot use the two
top options (Sort Ascending, Sort Descending).

Is there a way to have the protection of formulas in individual cells and
also have the ability to utilize the full functionality of the AutoFilter and
sorting features?

Thanks!

 

[AltaEgo]

Tools, protection, protect sheet. Check the option to use autofilter.

Using code:

Sub Workbook_Prot()

Const pw = "YourPassword"

'replace two instances of Sheet1 below with your sheet name

Sheet1.Protect Password:=pw, DrawingObjects:=True, _
contents:=True, Scenarios:=True
Sheet1.EnableAutoFilter = True

End Sub

Note: the above provides minimal protection. Study protection options if you
need additional protection and include extras. To find these, click on
"Protect" in the code module and press F1.

 

[Travis]

Thanks for the quick response Steve.

I enabled the Autofilter in the Tools/Protection/Protect Sheet/Options but
this still does not allow me to use the Sort "Ascending" Sort "Descending"
features within the Autofilter.

With the Protection/Sheet function enabled, all of the Autofilter functions
work (All, Top 10, Custom, Blanks, NonBlanks) but not the 'Sort Ascending" or
Sort Descending.

With the Protection/Sheet function enabled, when I try the "Sort Ascending"
or "Sort Descending" I get the following error message:
"The cell or chart you are trying to change is protected and therefore
read-only. To modify a protected cell or chart, first remove protection using
Unprotect Sheet command (Tools menu, Protection submenu). You may be prompted
for a password"

My main goal is to protect the formulas that are imbedded in specific ranges
of cells, these formulas bring data from one worksheet and dynamically update
them in other worksheets.

Thanks Steve

 

[AltaEgo]

Sorry, I tested sorting without locked cells.
You cannot sort without removing sheet protection. You will need to create a
macro to remove protection, sort and protect again:

Sub shSort()
Const pw = "yourPassword"

ActiveSheet.Unprotect Password:=pw
'record your sort macro using whole
'columns or named range.
'copy the code here Example below
Call mySort 'example recorded macro

ActiveSheet.Protect Password:=pw
End Sub

Sub mySort()
'
' Macro1 Macro
' Macro recorded by Steve
'

'
Columns("A:C").Select

Selection.sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("A2")
_
, Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=
_
xlSortNormal
End Sub


Depending how many sort options your user needs and how complex you need to
make it to achieve your aim, you might just use a couple of sort buttons or
further modify your code to call an input box to click or specify different
sort rows, named ranges, or some other method.