VBA - Password protect sheet AND allow filtering

[Gerald Higgins]

Hi all.

I'm having problems with a macro that should
a) Turn worksheet protection on for a specific worksheet
b) Require a password to turn it off
c) Allow filtering, while worksheet protection is on.

This is part of a larger AutoExecute macro that does a number of other things, which all work fine. These include turning protection on and off on other sheets (using a password), although none of the other sections deal with filtering.

This code turns protection on, and allows filtering. Which is good. But protection can then be manually turned off without requesting a password. Which is bad.

ActiveSheet.Protect "GERALD"
    ActiveSheet.EnableSelection = xlUnlockedCells
    ActiveSheet.Protect AllowFiltering:=True

This code turns protection on, and requires a password for manual unprotection. Which is good. But before protection is turned off, filtering can't be activated (even though the filter drop down arrows are clearly visible). Which is bad.

ActiveSheet.EnableSelection = xlUnlockedCells
    ActiveSheet.Protect AllowFiltering:=True
    ActiveSheet.Protect "GERALD"

I've tried every variation of this code that I can think of, but no luck yet.

Not sure if this is relevant, but all cells on the worksheet are locked, except for row 1, which contains the filter column headers.

As I'm still very much a beginner at VBA, there's probably something really basic that I am doing wrong. Can anyone point it out to me please ?

Thanks in advance for any help !

 

[Domenic]

Try...

With ActiveSheet
    .Protect Password:="GERALD", AllowFiltering:=True
    .EnableSelection = xlUnlockedCells
End With

 

[Gerald Higgins]

Domenic - thanks for the reply and the very useful lesson for this VBA novice in how to use the WITH technique.

It worked perfectly ! Thanks again.