PDA

View Full Version : VBA code to enable autofilter on protected sheets



magikmel
06-05-2010, 09:27 PM
Hi all,

Can someone please add to my code below? I need to enable autofilter on protected sheets 2 to 5.

Thanks in advance!! its really doing my head in:banghead:



Private Const myWBPassword As String = "test", _
mySHPassword As String = "test"
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim iSheet As Integer
On Error GoTo HE
For iSheet = 2 To 5
With ThisWorkbook.Sheets(iSheet)
.Unprotect mySHPassword
.Columns("D:J").EntireColumn.Hidden = True
.Protect mySHPassword
End With
Next iSheet
Sheet1.Activate
HE:
If Err.Number <> 0 Then
MsgBox "Error # " & Err.Number & " - " & Err.Description
End If
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim iSheet As Integer
Dim r As Range
On Error GoTo HE
For iSheet = 2 To 5
With ThisWorkbook.Sheets(iSheet)
.Unprotect mySHPassword
.Columns("D:J").EntireColumn.Hidden = True
.Protect mySHPassword

End With
Next iSheet
Sheet1.Activate
HE:
If Err.Number <> 0 Then
MsgBox "Error # " & Err.Number & " - " & Err.Description
End If
End Sub
Private Sub Workbook_Open()
Dim iSheet As Integer
On Error GoTo HE
If InputBox("Restricted Access Password Required") = myWBPassword Then
For iSheet = 2 To 5
With ThisWorkbook.Worksheets(iSheet)
.Unprotect mySHPassword
.Columns("D:J").EntireColumn.Hidden = False

End With
Next iSheet
End If
HE:
If Err.Number <> 0 Then
MsgBox "Error # " & Err.Number & " - " & Err.Description
End If
End Sub

Zack Barresse
06-06-2010, 08:44 AM
Hi there, welcome to the board!

I strongly recommend you look in your help files regarding the syntax of the Protect method. It really is all right there.