sheet protected > allow for macro to work

[bluepenink]

hello

i have the following macro in a worksheet

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    With ActiveSheet
        .AutoFilterMode = False
        .Range("myHeaders").AutoFilter
        If Range("myStoreNumber").Value <> 0 Then .Range("myHeaders").AutoFilter Field:=6, Criteria1:=Range("myStoreNumber").Value
    End With
    Application.ScreenUpdating = True
End Sub

i recently put a passowrd to protect all sheets, and basially, this macro is to filter the data in my table by store #....but after i put a password in..its not working

in VBA, i have the following code in "this workbook"..that i thought will enable the macro to work

Private Sub Workbook_Open()
For c = 1 To Sheets.Count
               With Worksheets(c)
        .Protect Password:="analyst", userinterfaceonly:=True
        .EnableOutlining = True
    End With
Next c
End Sub

can someone pls help!!

 

[dmtcrainey]

you need to Unprotect it before you make changes

Sheets(c).Unprotect Password:="analyst"

 

[bluepenink]

so does it become this now?

Private Sub Workbook_Open()
Sheets(c).Unprotect Password:="analyst"
For c = 1 To Sheets.Count
               With Worksheets(c)
        .Protect Password:="analyst", userinterfaceonly:=True, AllowFormattingCells:=True, Contents:=True, Scenarios:=True, _
            AllowFormattingColumns:=True, AllowFormattingRows:=True
        .EnableOutlining = True
    End With
Next c
End Sub

thx u?

 

[dmtcrainey]

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
With ActiveSheet
.Unprotect Password:="analyst"
.AutoFilterMode = False
.Range("myHeaders").AutoFilter
If Range("myStoreNumber").Value <> 0 Then .Range("myHeaders").AutoFilter Field:=6, Criteria1:=Range("myStoreNumber").Value
.Protect Password:="analyst", userinterfaceonly:=True, AllowFormattingCells:=True, Contents:=True, Scenarios:=True, _
AllowFormattingColumns:=True, AllowFormattingRows:=True
End With
Application.ScreenUpdating = True
End Sub


The code should protect workbook just fine, i was guessing it was an issue with editing the workbook.

 

[dmtcrainey]

sorry, disregard all that, I think what you are asking is this
Private Sub Workbook_Open()
For c = 1 To Sheets.Count
With Worksheets(c)
.Protect Password:="analyst", userinterfaceonly:=True, AllowFiltering:=True
.EnableOutlining = True
End With
Next c
End Sub

 

[bluepenink]

thxs dude..works like a charm