Removing filters and hidden columns VBA

[Affy99]

Hi All,

I have a workbook which has circa 30 tabs and is accessed byapproximately 25 users simultaneously.

I’m looking for come coding to remove any current filters and unhideany columns then re-protect each sheet on opening.

I have the following code which is removing the filters but doesn’t unhidethe columns and isn’t allowing the filter to be re-applied once the sheet isprotected:

Sub Autpen()
Dim xWs As Worksheet
For Each Wks InThisWorkbook.Worksheets
On Error Resume Next
If Wks.AutoFilterMode Then
Wks.AutoFilterMode =False
AllowFiltering = True
userinterfaceonly = True
End If
Next Wks
End Sub


Any help would be greatly appreciated.

Affy

 

[tonyyy]

Affy,

You might consider the following...

Sub Auto_Open()
Dim ws As Worksheet
On Error GoTo errHandler
For Each ws In ThisWorkbook.Worksheets
    With ws
        .Unprotect
        .Columns.Hidden = False
        If .AutoFilterMode = True Then .AutoFilterMode = False
        .Range("A1").AutoFilter
        .Rows(1).Locked = False
        .Protect AllowFiltering:=True, UserInterfaceOnly:=True
    End With
Next ws
errHandler:
    If Err.Number > 0 Then MsgBox "Sorry, an error occurred on Sheet " & ws.Name & vbCrLf _
    & Err.Number & " " & Err.Description
End Sub

In my quick research and simple testing, it seems the AllowFiltering property allows you to use an existing filter, not actually create one. The code places an autofilter into row 1.

The "On Error Resume Next" error checking has the potential to be dangerous in that it allows any error - code or content related - to be ignored without any warning to the user. It also makes it difficult to debug the code if it's on during testing.

Cheers,

tonyyy

 

[Affy99]

Hi Tonyyy,

Thank you very much for your reply. I will have a try with the code but I agree it may be dangerous to allow any error to be ignored.

Best Regards,

Affy