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
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