Using AutoFilter with worksheet protection in 2000 vs. 2003

[Guest]

I have created an Autofilter on 2 columns of a protected worksheet in 2000
using a macro that runs upon opening of the worksheet. I am encountering a
problem when a 2003 user tries to open the worksheet but cannot use the
AutoFilter dropdowns. I know that in 2003 it is much easier to use the
Autofilter on a protected worksheet with the AutoFilter option. Anyone have
any ideas how I can create the AutoFilter/Protected worksheet situation that
will work no matter what Excel version is being used?

 

[Jim Rech]

When I run this code from Workbook_Open in either Excel 2000 or 2003 auto
filtering seems to work okay on the protected sheet:

Sub ProtectSheet()
With Sheet1
.EnableAutoFilter = True
.Protect , True, True, True, True
End With
End Sub

--
Jim
|I have created an Autofilter on 2 columns of a protected worksheet in 2000
| using a macro that runs upon opening of the worksheet. I am encountering a
| problem when a 2003 user tries to open the worksheet but cannot use the
| AutoFilter dropdowns. I know that in 2003 it is much easier to use the
| Autofilter on a protected worksheet with the AutoFilter option. Anyone
have
| any ideas how I can create the AutoFilter/Protected worksheet situation
that
| will work no matter what Excel version is being used?

 

[Dave Peterson]

If you already have the outline applied, you can protect the worksheet in code
(auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
.EnableAutoFilter = True
End With
End Sub

It needs to be reset each time you open the workbook. (excel doesn't remember
it after closing the workbook.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

Thanks Dave! This worked fine.

If you already have the outline applied, you can protect the worksheet in code
(auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
.EnableAutoFilter = True
End With
End Sub

It needs to be reset each time you open the workbook. (excel doesn't remember
it after closing the workbook.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm