Using auto filter when worksheet is protected

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

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

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
>
>
>

Thanks Roger, I'll have a go

Tim

"Roger Govier" wrote:

> 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
>
>

Roger:
Thank you for this code it has been most helpful. One quick question. Now
that I have copied and pasted the code into a VBA module I experience an
error called "Run-time error '1004': Application-defined or object-defined
error". When I select the "Debug" button it highlights in yellow all the
code beginning with "ActiveSheet...through AllowFiltering..." I have no real
knowledge of VBA and was hoping you can assist in troubleshooting.
Thank you for reviewing my request. Dana Scully.

"Roger Govier" wrote:

> 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
>