Autofilter not working with protection

[A. Hendriks]

Hi there,

I created a protection for an excel sheet. I used the option "Allow users
to edit ranges". I created several ranges and one of the ranges is a group
called Admins, they can do anything. I selected in the "protect sheet" all
the options. If an user with the Admin group select the whole sheet the
user can not use the option autofilter, it is grey. Is this correct when
you use a protected option that this this option is unavaiable? We are
using Office XP. Thanks for the help.

Albert.

 

[Arvi Laanemets]

Hi

Into workbooks Open event, for every protected sheet, you want to allow to
use autofilter, add the code:
....
Sheets("Sheetname").Unprotect Password:="password"
Sheets("Sheetname").Protect Password:="password",
UserInterfaceOnly:=True
Sheets("Sheetname").EnableAutoFilter = True
....

 

[Idontknow]

Hi Arvi,

Thanks for your help but hmm I do get in the event? I tried visual basic
and there you had thisoption also and I put it on true but still not
working. I wonder if this is the correct way or not.

Albert

 

[Arvi Laanemets]

Hi

To create an Workbook's Open event:
Open the excel workbook (when not opened);
Activate VBA Editor (F11);
In VBAProject window (leftmost top one - when you don't see it, you have to
activate it (from View menu - Project Explorer), find your project (Excel
workbook), and double-click on ThisWorkbook;
The VBA window is opened. From left dropdown select Workbook - an empty
workbooks Open event is created. Fill in the code you need, and close VBA
editor;
Re-open the workbook for event to be triggered.

 

[Idontknow]

Dear Avri,

I tried what you said but everytime when I open the workbook I get the
error message

Run-time error '9':
Subscript out of range

I'm not sure what I'm doing wrong here. I followed your instructions. I
place your macro in the workbook event between

Private Sub workbook_Open()

and

End Sub.

I did something wrong here?

Thanks.
-

 

[Arvi Laanemets]

Hi

Did you replace Sheetname with your actual sheet name, and is it identical
to actual sheet name (no additional spaces, or some missing one etc.)?
To avoid such errors, I myself never use sheet names containing several
words. I.e. instead naming the sheet "My first sheet", I name it
"MyFirstSheet" or "MySheet1".

My code snippet was 3 rows - probably the second row was splitted
automatically by your newsreader.

When the only protected sheet, where you want to allow autofilter, is named
p.e. "Sheet1", and you protected with password "ForMeOnly", then the Open
event will be:
Private Sub workbook_Open()
Sheets("Sheet1").Unprotect Password:="ForMeOnly"
Sheets("Sheet1").Protect Password:="ForMeOnly", _
UserInterfaceOnly:=True
Sheets("Sheet1").EnableAutoFilter = True
End Sub

NB! to avoid splitting rows by newsreader, I splitted the 2nd code row in a
way, VBA is able to recognize it as continuing one.

 

[Idontknow]

Hi Avri,

It is working at last I'm a bit further I get know only an error that the
password is not correct. I have created many rules for this protection so I
need to figure this out. But that part of your macro seems to work. I can
not yet work with it because of the error message. THanks Avri for your
great help. Maybe I need your help again.

Albert