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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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?
 
Upvote 0
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.
 
Upvote 0
when you protect the sheet, put a check box in the options "use autofilter"
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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