How can I protect an excel worksheet containing filters?

Using Excel 2003 but need to share the worksheet with Excel 2000 users.I need
to be able to protect certain cells within the worksheet. A Macro is set-up
to sort certain info within the worksheet using various filters. When the
sheet is protected the filters no longer work. Any help would be appreciated.

JMcG, you could have the macro unprotect the sheet, sort the info and then
protect the sheet
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"JMcG" <[email protected]> wrote in message
news:[email protected]...
> Using Excel 2003 but need to share the worksheet with Excel 2000 users.I
> need
> to be able to protect certain cells within the worksheet. A Macro is
> set-up
> to sort certain info within the worksheet using various filters. When the
> sheet is protected the filters no longer work. Any help would be
> appreciated.

Use this in the Auto_open procedure in a General module:

Option Explicit
Sub auto_open()

Dim wks As Worksheet
Set wks = Worksheets("sheet1")

With wks
.Protect Password:="hi", userinterfaceonly:=True
.EnableAutoFilter = True
End With

End Sub

(you could use the workbook_open event under ThisWorkbook, too.)

This setting isn't remembered between closing/reopening the workbook. (So
Auto_open is a nice spot for it.)

JMcG wrote:
>
> Using Excel 2003 but need to share the worksheet with Excel 2000 users.I need
> to be able to protect certain cells within the worksheet. A Macro is set-up
> to sort certain info within the worksheet using various filters. When the
> sheet is protected the filters no longer work. Any help would be appreciated.

--

Dave Peterson

Hi,

I tried out your fix but unfortunately the problem still occurs. Thanks for
your help though.

"Paul B" wrote:

> JMcG, you could have the macro unprotect the sheet, sort the info and then
> protect the sheet
> --
> Paul B
> Always backup your data before trying something new
> Please post any response to the newsgroups so others can benefit from it
> Feedback on answers is always appreciated!
> Using Excel 2002 & 2003
>
> "JMcG" <[email protected]> wrote in message
> news:[email protected]...
> > Using Excel 2003 but need to share the worksheet with Excel 2000 users.I
> > need
> > to be able to protect certain cells within the worksheet. A Macro is
> > set-up
> > to sort certain info within the worksheet using various filters. When the
> > sheet is protected the filters no longer work. Any help would be
> > appreciated.
>
>
>

Hi Dave,

I'll give this a try and get back to you with the results.

"Dave Peterson" wrote:

> Use this in the Auto_open procedure in a General module:
>
> Option Explicit
> Sub auto_open()
>
> Dim wks As Worksheet
> Set wks = Worksheets("sheet1")
>
> With wks
> .Protect Password:="hi", userinterfaceonly:=True
> .EnableAutoFilter = True
> End With
>
> End Sub
>
> (you could use the workbook_open event under ThisWorkbook, too.)
>
> This setting isn't remembered between closing/reopening the workbook. (So
> Auto_open is a nice spot for it.)
>
> JMcG wrote:
> >
> > Using Excel 2003 but need to share the worksheet with Excel 2000 users.I need
> > to be able to protect certain cells within the worksheet. A Macro is set-up
> > to sort certain info within the worksheet using various filters. When the
> > sheet is protected the filters no longer work. Any help would be appreciated.
>
> --
>
> Dave Peterson
>