VBA syntax for ActiveSheet.Protect when using variable for password

[kcmuppet]

Hello,

I found some excellent code to add and delete table rows in protected sheets here: Insert & Delete Table Rows With Worksheet Protection — The Spreadsheet Guru

I'm trying to modify it to add enable filtering and sorting but confused about the syntax and what I've tried is working so far

The author's code seems to set a variable for the password

Dim Password As String
Password = ""

which is then used like this:

ActiveSheet.Protect Password

So I thought I could get it allow filtering by doing this:

ActiveSheet.Protect Password, AllowFiltering:=True

But that doesn't compile, so based on this: Excel VBA protect worksheet with password, I tried this:

ActiveSheet.Protect (Password=Password, AllowFiltering)

But that doesn't work either.

What is the correct syntax?

 

[Fluff]

Are you actually using a password?

 

[kcmuppet]

Are you actually using a password?

I will be

 

[Fluff]

Ok, how about

Dim Pword As String
Pword = ""
ActiveSheet.Protect Password:=Pword, AllowFiltering:=True

You should never use VBA keywords for variables, as it can cause problems.

 

[kcmuppet]

Ok, how about

Dim Pword As String
Pword = ""
ActiveSheet.Protect Password:=Pword, AllowFiltering:=True

You should never use VBA keywords for variables, as it can cause problems.

Thanks, that makes sense now.

However, oddly, now that I'm specifying it that way, does that mean I have to specify True/False every possible protection?

(I say that because, it now doesn't seem to respect the default protections (select locked & unlocked cells) i.e. it lets me delete cells with formulae/hide columns, etc.)

 

[Fluff]

You should only be able to delete cells that are unlocked.

 

[kcmuppet]

Many thanks, I have it working as expected now.

For anyone following this thread I have a linked question here.

 

[Fluff]

Glad it's working & thanks for the feedback.