VBA syntax for ActiveSheet.Protect when using variable for password

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
272
Office Version
  1. 365
  2. 2010
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
VBA Code:
Dim Password As String
Password = ""

which is then used like this:
VBA Code:
ActiveSheet.Protect Password

So I thought I could get it allow filtering by doing this:
VBA Code:
ActiveSheet.Protect Password, AllowFiltering:=True
But that doesn't compile, so based on this: Excel VBA protect worksheet with password, I tried this:
VBA Code:
ActiveSheet.Protect (Password=Password, AllowFiltering)

But that doesn't work either.

What is the correct syntax?
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
Are you actually using a password?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
Dim Pword As String
Pword = ""
ActiveSheet.Protect Password:=Pword, AllowFiltering:=True
You should never use VBA keywords for variables, as it can cause problems.
 
Solution

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
272
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

Ok, how about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
You should only be able to delete cells that are unlocked.
 

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
272
Office Version
  1. 365
  2. 2010
Many thanks, I have it working as expected now.

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
Glad it's working & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,129
Messages
5,628,865
Members
416,347
Latest member
AT2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top