Using auto filter when worksheet is protected

[Guest]

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

 

[Roger Govier]

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

 

[Ron de Bruin]

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

 

[Guest]

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

I'll have another try

 

[Guest]

Thanks Roger, I'll have a go

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

 

[Debra Dalgleish]

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.

 

[Guest]

Thank you everyone for your advice

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.

 

[Guest]

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.