MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Filters on a protected worksheet

Posted by Andrew Laycock on September 13, 2000 2:21 AM

I have several worksheets which need to be protected
for business purposes.
However, once they are protected the filters
cannot be used.
Is there a way of still having a protected sheet and
being able to use the filters?

Posted by Ivan Moala on September 14, 0100 3:17 AM

Here is one way to do this via VBA
This method is one I adapted from Bob Umlas so give credit to him.

In a sheet module (the one with the filters)
put this code in.

Option Explicit
Public bflag As Boolean

Private Sub Worksheet_Calculate()
Static bflag

If bflag Then Exit Sub
On Error Resume Next
With Application
.EnableEvents = False
.EnableEvents = True
End With
End Sub

Now in A1 place this formula;
formula in cell IV1.


Now in a module put this code in;

Public bflag

'Run this to deactivate it if you want
'to edit the sheet
Sub ChangeProtectMode()
Static bflag

Dim PW As String
'Run this routine again to Protect the sheet 'IFM
If bflag Then
bflag = False
Range("IV1").FormulaR1C1 = "=COUNTA(R[1]C:R[65535]C,C[-255]:C[-1])"
Exit Sub
PW = InputBox("Password......")
If PW <> "test" Then End
'Replace formula
Range("IV1") = 1
bflag = True
End If
End Sub

The filtering of a list does not trigger the Calculate event. BUT any event that does activates
the Calculate event will trigger this event proc
and undo anything done, so in effect protecting
the sheet.


Posted by Andrew Laycock on September 14, 0100 6:38 AM

I've tried the above solution but nothing happens.
I'm also a little unclear on the formula and whether
it is to be placed in A1 or IV1.
Further assistance would be appreciated.



Posted by Ivan Moala on September 14, 0100 11:52 PM

probably best if I send you an example
Sorr i put you wrong, should be in IV1

Example to come.