View Full Version : Auto-filter turning off in protected worksheet
cduckett
09-19-2012, 12:16 PM
I have a protected sheet with an auto-filter setup on a particular column (F) that works fine in protected mode, UNTIL I add any values anywhere on the worksheet, then the filter turns itself off.
Only way to re-enable it is to unprotect worksheet, protect sheet and allow users to "use autofilter."
Is there any way to fix this, or a code I can write into VBA to alleviate this problem?
Thanks in advance!
cduckett
09-19-2012, 12:36 PM
Ok, I've figured out that a code I previously wrote into VBA is affecting this.
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "R9:R1048576" '<<<< change to suit
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Unprotect Password:="pw"
With Target
Target.EntireRow.Locked = .Value = "Yes"
End With
End If
ws_exit:
Me.Protect Password:="pw"
Application.EnableEvents = True
End Sub
When this is enabled, it causes Auto-filter to become disabled whenever new values are written into any of the cells.
Is there anyway to have both work? The original code causes a row to become "locked" after a value of "yes" is chosen in the last column.
I would like for values in a different column to be filtered as new data is entered into the worksheet.
cduckett
09-19-2012, 01:29 PM
Figured it out
ws_exit:
Me.Protect AllowFiltering:=True, _
Password:="iberia"
Application.EnableEvents = True
End Sub
Had to allow filtering in the code
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.