Using auto filter when worksheet is protected

T

Tim

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
 
R

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
 
R

Ron de Bruin

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

Tim

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

I'll have another try
 
T

Tim

Thanks Roger, I'll have a go

Tim

Roger Govier said:
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
Click to expand...
 
D

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

Tim

Thank you everyone for your advice

Regards

Tim

Debra Dalgleish said:
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.
Click to expand...
 
D

Dana Scully

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