Sort on Protected Worksheet

[steve]

I need to Sort on a protected worksheet.

I saw in another discussion some VBA code to place
in "ThisWorkbook" -- this code will allow Autofilter on a
protected worksheet. Here is that code:

Private Sub Workbook_Open()
'check for filter, turn on if none exists
With Worksheets("Output")
If Not .AutoFilterMode Then
.Range("A2").AutoFilter
End If
.EnableAutoFilter = True
' .EnableSort = True
.Protect Password:="wwca", _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub

I added code of ".EnableSort = True" but it is not valid
code. What is the valid code for this. I am using Excel
2000. Thanks.

 

[Frank Kabel]

Hi
try
Private Sub Workbook_Open()
'check for filter, turn on if none exists
With Worksheets("Output")
If Not .AutoFilterMode Then
.Range("A2").AutoFilter
End If
.EnableAutoFilter = True
.Protect Password:="wwca", _
Contents:=True, UserInterfaceOnly:=True, AllowSort:=true
End With
End Sub

 

[Steve]

The "AllowSort:=True" did not work -- after I enable macro
I need to debug the code. Any other suggestions? Also, I
want to be able to hide or unhide columns/rows after
protecting the sheet. Any suggestions for that, too?

 

[Frank Kabel]

Hi Steve
what Excel version are you using. Not sure when this feature was
introduced

 

[steve]

Excel 2000

-----Original Message-----
Hi Steve
what Excel version are you using. Not sure when this feature was
introduced

--
Regards
Frank Kabel
Frankfurt, Germany



.

 

[Frank Kabel]

Hi
AFAIK this feature was in troduced with Excel 2002.

 

[steve]

So, there is no solution? Can you pass along the code you
would use to allow hiding/unhiding columns and rows,
please. Thanks.

 

[Frank Kabel]

Hi
i would create a specific macro for this which first unprotects the
worksheets and then hides/undiges the rows. Afterwards the macro
protects the worksheet again. so something like

sub hide_it()
with activesheet
.unprotect password:="your_password"
activecell.entirerow.hidden=true
.protect password:="your_password"
end with
end sub

 

[steve]

Thanks for all of the information. This is a spreadsheet I
will send to others, so I wanted to lock the formulas but
allow the other users to hide or unhide columns at will.
These are not experienced MS Excel users, so I don't want
to add any complexities for them (I thought about have a
popup window where they can enter the range of columns to
hide/unhide, but that might be quite a bit of work).

Give our prior commucations, I probably need to get Excel
2002 -- short of that, is there a way to protect the
formulas, and still have the use hide or unhide any
columns they choose?

 

[Frank Kabel]

Hi Steve
don't see a better solution. Sorry

 

[sseyer]

You must use AllowSorting:=True

Here's a sample:
Worksheets("UpdateMaterials").Protect Password:=PW2, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFormattingCells:=True, AllowFiltering:=True, AllowSorting:=True

Cheers
SJ