Autofilter on protected worksheet ??!?! 2

[Klopper]

Hi

I would like users of an Excel 97 spreadsheet to be able to filter rows on a worksheet using the autofilter function but without being able to modify any data within the cells.

Can this be done? If I turn on the worksheet protection, the autofilter cannot be used.....

TIA
Klopper

 

[SkipVought]

Hi,
Autofilter cannot be used on a protected sheet; you cannot add an Autofilter on a protected sheet and if an AUtofilter has already been added but protection is on, the filter dropdowns will not work. You would have to control it all in VBA if you so desired. Skip,
metzgsk@voughtaircraft.com

 

[Klopper]

Thanks SKip, you have confirmed my suspicions.

 

[DaleWatson123321]

TIA,

A bit of "food for thought"...

You could place a "button" at the top of your PROTECTED sheet, and have code attached to the button which would...

...extract all the data to a SEPARATE sheet which has pre-set autofilters, and is UNPROTECTED specifically for use by the end-user.

Or, perhaps better still, have the "button" at the top of the SEPARATE UNPROTECTED sheet, and have it say something like "Click Here to Update the Data Below - before using the Autofilter functions"

If you would like help with the extraction code, I can email you a simple example file. Just email me and I'll return the file.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[SkipVought]

Dale's suggestion is good. You could use this code with your button on the protected sheet (Just remember to assign the correct sheet names to data and filter)...

' assign you sheet names here
    MyDataSheet = "Data"
    MyFilterSheet = "Filter"
    With Worksheets(MyFilterSheet)
        .Activate
        .Cells.Clear
    End With
    
    With Worksheets(MyDataSheet)
        .Activate
        .Unprotect
        .Cells(1, 1).CurrentRegion.Copy Destination:=Worksheets("Filter").Cells(1, 1)
        .Protect
    End With
    
    With Worksheets(MyFilterSheet)
        .Activate
        .Cells(1, 1).CurrentRegion.AutoFilter
    End With

Skip,
metzgsk@voughtaircraft.com

 

[DaleWatson123321]

Klopper,

...curious to know how you've made out with both Skip's and my latest suggestions...

Don't hesitate to ask for further help if needed, and as mentioned, I have an example file I can send.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Klopper]

Skip, Dale

Thanks for the suggestions.
I think I use code attached to a button which replicates the autofilter function; this will use criteria the user has selected from a listbox or similar.

eg:

FilterCriteria1 = Range("A1&quot.value
worksheet.unprotect
range("A2:H2&quot.AutoFilter Field:=2, Criteria1:= FilterCriteria1
worksheet.protect

Rgds
Klopper

 

[Klopper]

Actually, scratch that last post as a colleague suggested a much more elegant solution: use the worksheet_change event to undo whatever the user has done!

I have removed the protection from the worksheet, reinstated the autofilter, then pasted the code below into the sheet module:

Option Explicit
Option Base 0
Public BCHANGE As Boolean

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Sheets("sheet1&quot.Range("A1&quot.Value = "." Then
BCHANGE = False
Sheets("sheet1&quot.Range("A1&quot.Value = ""
BCHANGE = False
End If

If BCHANGE = True Then
If Sheets(&quot;sheet1&quot.Range(&quot;A1&quot.Value <> &quot;You can Edit&quot; Then
BCHANGE = False
Application.Undo
MsgBox &quot;You cannot change any of the data on this sheet&quot;, _
vbCritical, &quot;No Changes allowed&quot;
End If
End If

BCHANGE = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
BCHANGE = True
End Sub

 

[Klopper]

Eureka
If the &quot;userinterfaceonly&quot; argument of the sheets.protect method is set to &quot;true&quot;, then the worksheet can be filtered using the autofilter whilst the data cannot be changed.

eg.
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect contents:=True, userinterfaceonly:=True

As always, it is simple when you know how ;-)
Klopper

 

[DaleWatson123321]

Klopper,

Thanks for that last tip !!! ...definitely worthy of a STAR.

Perhaps you should consider posting the tip as a &quot;FAQ&quot; ?

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[SkipVought]

Klopper,

I agree, too! Way 2 go! Skip,
metzgsk@voughtaircraft.com