Quick Question - Why isn't this simple code working [locked/protection]

[Grizlore]

Hi, i am sure this is something simple.

Could anyone tell me why this code isnt working please?

Basically, i should lock the entire row IF the word "completed" is in column "O"

Sub Lock_Completed()

Application.ScreenUpdating = False

    With Sheets("AUDITS")
    .Unprotect "password"
    .Cells.Locked = True
        With .Columns("O")
        .AutoFilter Field:=1, Criteria1:="<>Completed"
        On Error Resume Next
        .SpecialCells(xlCellTypeVisible).EntireRow.Locked = True
        On Error GoTo 0
        End With
    .ShowAllData
    .Protect "password"
    End With

Application.ScreenUpdating = True

End Sub

Any help would be appreciated

 

[JamesW]

You are locking everything (.cells.locked = true) and then you are locking the entire row of the cells where completed is NOT true.

Try:

Sub Lock_Completed()

Application.ScreenUpdating = False

    With Sheets("AUDITS")
    .Unprotect "password"
    .Cells.Locked = False
        With .Columns("O")
        .AutoFilter Field:=1, Criteria1:="Completed"
        On Error Resume Next
        .SpecialCells(xlCellTypeVisible).EntireRow.Locked = True
        On Error GoTo 0
        End With
    .ShowAllData
    .Protect "password"
    End With

Application.ScreenUpdating = True

End Sub

 

[Grizlore]

Thanks

That code only locks the header row? (the cell O1 is named "OUTSTANDING / COMPLETED")


Can't work out why that is this is happening

any ideas?

 

[JamesW]

It locks all completed rows when I tried it

Is your first filter on column O? .AutoFilter Field:=1 will put an auto filter on the first field with a filter (if that makes sense).

 

[Grizlore]

we are getting somewhere....

if I change to...

    .AutoFilter Field:=15, Criteria1:="completed"

then it work.

However, i would like to allow the filters to work (so you can select them) however the code doenst allow this. Would you happen to have thoughts on why?

Many thanks for your help, much appreciated

 

[JamesW]

Change the .Protect "password" to:

    .Protect "password", AllowFiltering:=True

 

[Grizlore]

Marvellous

Many Thanks !