Using auto filter when worksheet is protected

Note:
In 2002-2003 this is a option when you protect your sheet.
No need for VBA code then


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Roger Govier" <roger@nospamtechnology4u.co.uk> wrote in message news:43874646.2080504@nospamtechnology4u.co.uk...
> Hi Tim
>
> You need to add the following code to a module in the workbook
>
> Sub auto_open()
> With Worksheets("sheet1")
> ActiveSheet.Protect Password:="roger", _
> DrawingObjects:=True, _
> Contents:=True, Scenarios:=True, _
> AllowFiltering:=True
> End With
> End Sub
>
> Change Password to whatever is used by you.
>
> You can copy the code I posted and paste it into your Visual Basic Editor
> (VBE) in a Standard Module located in your Workbook. Shortcut
> keys would be ..
>
> Alt + F11 (open VBE)
> Ctrl + R (open Project Explorer)
> Select file on left
> If no modules exist:
> Insert | Module
> Paste code in Module
> If modules exist:
> Double click desired module
> Paste code in Module
>
> Regards
>
> Roger Govier
>
>
> Tim wrote:
>> Hello
>>
>> I'm just adding the finishing touches to a spreadsheet and a large area of one of the worksheets has been protected. However the
>> spreadsheet is designed with the functionality of autofilter in mind. When I protect the sheet it no longer gives the ability to
>> auto filter. Is this just the way it is or does anyone have any suggestions
>>
>> Thanks in advance
>>
>> Regards
>>
>> Tim

I have Office 2003 Pro and once protected the option just "fades" out.

I'll have another try

"Ron de Bruin" wrote:

> Note:
> In 2002-2003 this is a option when you protect your sheet.
> No need for VBA code then
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Roger Govier" <roger@nospamtechnology4u.co.uk> wrote in message news:43874646.2080504@nospamtechnology4u.co.uk...
> > Hi Tim
> >
> > You need to add the following code to a module in the workbook
> >
> > Sub auto_open()
> > With Worksheets("sheet1")
> > ActiveSheet.Protect Password:="roger", _
> > DrawingObjects:=True, _
> > Contents:=True, Scenarios:=True, _
> > AllowFiltering:=True
> > End With
> > End Sub
> >
> > Change Password to whatever is used by you.
> >
> > You can copy the code I posted and paste it into your Visual Basic Editor
> > (VBE) in a Standard Module located in your Workbook. Shortcut
> > keys would be ..
> >
> > Alt + F11 (open VBE)
> > Ctrl + R (open Project Explorer)
> > Select file on left
> > If no modules exist:
> > Insert | Module
> > Paste code in Module
> > If modules exist:
> > Double click desired module
> > Paste code in Module
> >
> > Regards
> >
> > Roger Govier
> >
> >
> > Tim wrote:
> >> Hello
> >>
> >> I'm just adding the finishing touches to a spreadsheet and a large area of one of the worksheets has been protected. However the
> >> spreadsheet is designed with the functionality of autofilter in mind. When I protect the sheet it no longer gives the ability to
> >> auto filter. Is this just the way it is or does anyone have any suggestions
> >>
> >> Thanks in advance
> >>
> >> Regards
> >>
> >> Tim
>
>
>

Whether you use Ron's suggestion, or Roger's code, you can only work
with existing AutoFilters on a protected worksheet. You can't create new
ones, or delete existing ones, so the menu command will be unavailable.

Tim wrote:
> I have Office 2003 Pro and once protected the option just "fades" out.
>
> I'll have another try
>
> "Ron de Bruin" wrote:
>
>
>>Note:
>>In 2002-2003 this is a option when you protect your sheet.
>>No need for VBA code then
>>
>>
>>--
>>Regards Ron de Bruin
>>http://www.rondebruin.nl
>>
>>
>>"Roger Govier" <roger@nospamtechnology4u.co.uk> wrote in message news:43874646.2080504@nospamtechnology4u.co.uk...
>>
>>>Hi Tim
>>>
>>>You need to add the following code to a module in the workbook
>>>
>>>Sub auto_open()
>>> With Worksheets("sheet1")
>>> ActiveSheet.Protect Password:="roger", _
>>> DrawingObjects:=True, _
>>> Contents:=True, Scenarios:=True, _
>>> AllowFiltering:=True
>>> End With
>>>End Sub
>>>
>>>Change Password to whatever is used by you.
>>>
>>>You can copy the code I posted and paste it into your Visual Basic Editor
>>>(VBE) in a Standard Module located in your Workbook. Shortcut
>>>keys would be ..
>>>
>>>Alt + F11 (open VBE)
>>>Ctrl + R (open Project Explorer)
>>>Select file on left
>>>If no modules exist:
>>> Insert | Module
>>> Paste code in Module
>>>If modules exist:
>>> Double click desired module
>>> Paste code in Module
>>>
>>>Regards
>>>
>>>Roger Govier
>>>
>>>
>>>Tim wrote:
>>>
>>>>Hello
>>>>
>>>>I'm just adding the finishing touches to a spreadsheet and a large area of one of the worksheets has been protected. However the
>>>>spreadsheet is designed with the functionality of autofilter in mind. When I protect the sheet it no longer gives the ability to
>>>>auto filter. Is this just the way it is or does anyone have any suggestions
>>>>
>>>>Thanks in advance
>>>>
>>>>Regards
>>>>
>>>>Tim
>>>
>>
>>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thank you everyone for your advice

Regards

Tim

"Debra Dalgleish" wrote:

> Whether you use Ron's suggestion, or Roger's code, you can only work
> with existing AutoFilters on a protected worksheet. You can't create new
> ones, or delete existing ones, so the menu command will be unavailable.
>
> Tim wrote:
> > I have Office 2003 Pro and once protected the option just "fades" out.
> >
> > I'll have another try
> >
> > "Ron de Bruin" wrote:
> >
> >
> >>Note:
> >>In 2002-2003 this is a option when you protect your sheet.
> >>No need for VBA code then
> >>
> >>
> >>--
> >>Regards Ron de Bruin
> >>http://www.rondebruin.nl
> >>
> >>
> >>"Roger Govier" <roger@nospamtechnology4u.co.uk> wrote in message news:43874646.2080504@nospamtechnology4u.co.uk...
> >>
> >>>Hi Tim
> >>>
> >>>You need to add the following code to a module in the workbook
> >>>
> >>>Sub auto_open()
> >>> With Worksheets("sheet1")
> >>> ActiveSheet.Protect Password:="roger", _
> >>> DrawingObjects:=True, _
> >>> Contents:=True, Scenarios:=True, _
> >>> AllowFiltering:=True
> >>> End With
> >>>End Sub
> >>>
> >>>Change Password to whatever is used by you.
> >>>
> >>>You can copy the code I posted and paste it into your Visual Basic Editor
> >>>(VBE) in a Standard Module located in your Workbook. Shortcut
> >>>keys would be ..
> >>>
> >>>Alt + F11 (open VBE)
> >>>Ctrl + R (open Project Explorer)
> >>>Select file on left
> >>>If no modules exist:
> >>> Insert | Module
> >>> Paste code in Module
> >>>If modules exist:
> >>> Double click desired module
> >>> Paste code in Module
> >>>
> >>>Regards
> >>>
> >>>Roger Govier
> >>>
> >>>
> >>>Tim wrote:
> >>>
> >>>>Hello
> >>>>
> >>>>I'm just adding the finishing touches to a spreadsheet and a large area of one of the worksheets has been protected. However the
> >>>>spreadsheet is designed with the functionality of autofilter in mind. When I protect the sheet it no longer gives the ability to
> >>>>auto filter. Is this just the way it is or does anyone have any suggestions
> >>>>
> >>>>Thanks in advance
> >>>>
> >>>>Regards
> >>>>
> >>>>Tim
> >>>
> >>
> >>
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>