worksheet protection

Colleen45

Active Member
Joined
Jul 22, 2007
Messages
495
This should be relatively simple to solve.
On my worksheet i am using advanced filters to view the data in the sheet.
But when I protect the sheet they do not work, I have unlocked those particular cells (Row 1). But it still does not allow the use of the advanced filter when the sheet protection is on. Any ideas?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,750
Advanced filters are allowed even if the sheet is protected.

Which specific advanced filtering are you using? The one with filtering criteria? The unque values filtering?
 

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,750
SORRY advanced filter should read "autofilter"
Ahh, in that case, when you protect the sheet, make sure you check the USE AUTOFILTER checkbox under the ALLOW USERS OF THIS WORKSHEET TO window.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
when you protect the sheet, put a check box in the options "use autofilter"
 

Colleen45

Active Member
Joined
Jul 22, 2007
Messages
495
Uhmm i think i spoke to soon. When the sheet is protected, and i did as you suggested, Yes it does work fine. But if i click on a cell that uses a VBA routine, the autofilter stops working, any ideas why
 

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,750
Uhmm i think i spoke to soon. When the sheet is protected, and i did as you suggested, Yes it does work fine. But if i click on a cell that uses a VBA routine, the autofilter stops working, any ideas why

It could be that the VBA codes you are running involves protecting the sheet without allowing autofilter.

Press ALT+F11, look for the module that contains the VBA code you were running, then copy and paste it here so we can see if it is the one causing the problem.
 

Colleen45

Active Member
Joined
Jul 22, 2007
Messages
495
This is the VBA code,
The 'protectinterfaceonly" is in because, when i would protect the worksheet, this macro would give a error, so essentially this stops that error.
But have a lookl and see if something can be added to fix the hiccup, ps this code works 100%

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 1 Or Target.Column = 18 Then
Target.Parent.Protect Userinterfaceonly:=True
If Target.Value > 0 Then
Target.Offset(0, 2).Value = Date
Else
Target.Offset(0, 2).Value = ""
End If
End If
End Sub

Thank you
 

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,750
Try

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 1 Or Target.Column = 18 Then
Target.Parent.Protect Userinterfaceonly:=True, AllowFiltering:=True
If Target.Value > 0 Then
Target.Offset(0, 2).Value = Date
Else
Target.Offset(0, 2).Value = ""
End If
End If
End Sub

NOTE: I have not tested these codes.
 

Forum statistics

Threads
1,181,055
Messages
5,927,863
Members
436,573
Latest member
CMR237

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top